Need google query to only show sum of items greater than value from cell and another query to sum values...
up vote
0
down vote
favorite
I have 2 queries which need to be answered:
A)
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
I would like to add the condition to only show values greater than what is entered in cell 'S3'. I tried adding AND sum(I) >= "&S3&" but it showed an error. I tried with shifting quotes but nothing. How do I achieve this? Maybe with another query?
B)
=QUERY(Data!B:I,"select E, sum(I) where D is not null AND B contains '"&G4&"' AND C >= date '"&TEXT(G2,"yyyy-mm-dd")&"' AND C <= date '"&TEXT(I2,"yyyy-mm-dd")&"' group by E pivot G order by E",1)
I have a drop down list in cell 'I3' with 2 options, 'الكمية' and 'القيمة' and I would like the query to give me sum of values in 'I' in case of 'الكمية' and 'N' in case of 'القيمة'. How do I achieve it?
Thank you.
google-sheets
asked Nov 11 at 8:33
Huzaifa Ezzi
25
add a comment |
up vote
0
down vote
favorite
I have 2 queries which need to be answered:
A)
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
I would like to add the condition to only show values greater than what is entered in cell 'S3'. I tried adding AND sum(I) >= "&S3&" but it showed an error. I tried with shifting quotes but nothing. How do I achieve this? Maybe with another query?
B)
=QUERY(Data!B:I,"select E, sum(I) where D is not null AND B contains '"&G4&"' AND C >= date '"&TEXT(G2,"yyyy-mm-dd")&"' AND C <= date '"&TEXT(I2,"yyyy-mm-dd")&"' group by E pivot G order by E",1)
I have a drop down list in cell 'I3' with 2 options, 'الكمية' and 'القيمة' and I would like the query to give me sum of values in 'I' in case of 'الكمية' and 'N' in case of 'القيمة'. How do I achieve it?
Thank you.
google-sheets
asked Nov 11 at 8:33
Huzaifa Ezzi
25
Maybe post a slightly modified version of the question, with the arabic characters replaced by English. A link to a test sheet with a minimal problem will also help to get attention.
– bcperth
Nov 11 at 22:32
I'll try to answer both the question in one reply. Firstly, lets use the English term 'quantity' for 'الكمية' and 'value' for 'القيمة' to clear things a bit. 'Quantity' falls under column 'I' and 'value' under 'N.' Now to answer @JPV's question, your solution will only give me quantities greater than that is 'S3' without summing up column 'I' whereas I'm looking for accumulated quantity and not individually sold.
– Huzaifa Ezzi
Nov 12 at 10:49
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have 2 queries which need to be answered:
A)
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
I would like to add the condition to only show values greater than what is entered in cell 'S3'. I tried adding AND sum(I) >= "&S3&" but it showed an error. I tried with shifting quotes but nothing. How do I achieve this? Maybe with another query?
B)
=QUERY(Data!B:I,"select E, sum(I) where D is not null AND B contains '"&G4&"' AND C >= date '"&TEXT(G2,"yyyy-mm-dd")&"' AND C <= date '"&TEXT(I2,"yyyy-mm-dd")&"' group by E pivot G order by E",1)
I have a drop down list in cell 'I3' with 2 options, 'الكمية' and 'القيمة' and I would like the query to give me sum of values in 'I' in case of 'الكمية' and 'N' in case of 'القيمة'. How do I achieve it?
Thank you.
google-sheets
asked Nov 11 at 8:33
Huzaifa Ezzi
25
I have 2 queries which need to be answered:
A)
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
I would like to add the condition to only show values greater than what is entered in cell 'S3'. I tried adding AND sum(I) >= "&S3&" but it showed an error. I tried with shifting quotes but nothing. How do I achieve this? Maybe with another query?
B)
=QUERY(Data!B:I,"select E, sum(I) where D is not null AND B contains '"&G4&"' AND C >= date '"&TEXT(G2,"yyyy-mm-dd")&"' AND C <= date '"&TEXT(I2,"yyyy-mm-dd")&"' group by E pivot G order by E",1)
I have a drop down list in cell 'I3' with 2 options, 'الكمية' and 'القيمة' and I would like the query to give me sum of values in 'I' in case of 'الكمية' and 'N' in case of 'القيمة'. How do I achieve it?
Thank you.
google-sheets
google-sheets
asked Nov 11 at 8:33
Huzaifa Ezzi
25
asked Nov 11 at 8:33
Huzaifa Ezzi
25
asked Nov 11 at 8:33
Huzaifa Ezzi
25
asked Nov 11 at 8:33
Huzaifa Ezzi
25
asked Nov 11 at 8:33
Huzaifa Ezzi
25
25
Maybe post a slightly modified version of the question, with the arabic characters replaced by English. A link to a test sheet with a minimal problem will also help to get attention.
– bcperth
Nov 11 at 22:32
I'll try to answer both the question in one reply. Firstly, lets use the English term 'quantity' for 'الكمية' and 'value' for 'القيمة' to clear things a bit. 'Quantity' falls under column 'I' and 'value' under 'N.' Now to answer @JPV's question, your solution will only give me quantities greater than that is 'S3' without summing up column 'I' whereas I'm looking for accumulated quantity and not individually sold.
– Huzaifa Ezzi
Nov 12 at 10:49
add a comment |
Maybe post a slightly modified version of the question, with the arabic characters replaced by English. A link to a test sheet with a minimal problem will also help to get attention.
– bcperth
Nov 11 at 22:32
I'll try to answer both the question in one reply. Firstly, lets use the English term 'quantity' for 'الكمية' and 'value' for 'القيمة' to clear things a bit. 'Quantity' falls under column 'I' and 'value' under 'N.' Now to answer @JPV's question, your solution will only give me quantities greater than that is 'S3' without summing up column 'I' whereas I'm looking for accumulated quantity and not individually sold.
– Huzaifa Ezzi
Nov 12 at 10:49
Maybe post a slightly modified version of the question, with the arabic characters replaced by English. A link to a test sheet with a minimal problem will also help to get attention.
– bcperth
Nov 11 at 22:32
Maybe post a slightly modified version of the question, with the arabic characters replaced by English. A link to a test sheet with a minimal problem will also help to get attention.
– bcperth
Nov 11 at 22:32
I'll try to answer both the question in one reply. Firstly, lets use the English term 'quantity' for 'الكمية' and 'value' for 'القيمة' to clear things a bit. 'Quantity' falls under column 'I' and 'value' under 'N.' Now to answer @JPV's question, your solution will only give me quantities greater than that is 'S3' without summing up column 'I' whereas I'm looking for accumulated quantity and not individually sold.
– Huzaifa Ezzi
Nov 12 at 10:49
I'll try to answer both the question in one reply. Firstly, lets use the English term 'quantity' for 'الكمية' and 'value' for 'القيمة' to clear things a bit. 'Quantity' falls under column 'I' and 'value' under 'N.' Now to answer @JPV's question, your solution will only give me quantities greater than that is 'S3' without summing up column 'I' whereas I'm looking for accumulated quantity and not individually sold.
– Huzaifa Ezzi
Nov 12 at 10:49
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
Try
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND I >= "&S3&" AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
answered Nov 12 at 12:29
JPV
10.3k21425
Hey! Your solution worked like a charm! Thanks a million!!! Please help me solve the other query as well.
– Huzaifa Ezzi
Nov 12 at 16:21
Hi! Sorry, but I did not test your solution before posting the comment. There seems to be an error. It only filters the rows which consist of values greater than what I enter in 'S3' whereas I need it to filter the summed up quantity. Here is the link to the sheet. Please copy the data and see if you can get a solution. Thank you.
– Huzaifa Ezzi
Nov 12 at 17:48
@HuzaifaEzzi: Ok. so you want the summed values in I to be >= than S3? Can"t access your spreadsheet, please fix permissions.
– JPV
Nov 13 at 8:14
Yes, that is correct. Please find the query in cell 'Q7' of from the Farmers' Interactive Summary sheet of this file. . Thank you.
– Huzaifa Ezzi
Nov 15 at 6:57
I'd suggest do the sum of column I in one query, then wrap another query around it, filtering out the summed rows that do not meet the condition. Something like =QUERY(QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1), "where Col2I >= "&T3&"")
– JPV
Nov 15 at 8:08
|
show 1 more comment
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Try
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND I >= "&S3&" AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
answered Nov 12 at 12:29
JPV
10.3k21425
Hey! Your solution worked like a charm! Thanks a million!!! Please help me solve the other query as well.
– Huzaifa Ezzi
Nov 12 at 16:21
Hi! Sorry, but I did not test your solution before posting the comment. There seems to be an error. It only filters the rows which consist of values greater than what I enter in 'S3' whereas I need it to filter the summed up quantity. Here is the link to the sheet. Please copy the data and see if you can get a solution. Thank you.
– Huzaifa Ezzi
Nov 12 at 17:48
@HuzaifaEzzi: Ok. so you want the summed values in I to be >= than S3? Can"t access your spreadsheet, please fix permissions.
– JPV
Nov 13 at 8:14
Yes, that is correct. Please find the query in cell 'Q7' of from the Farmers' Interactive Summary sheet of this file. . Thank you.
– Huzaifa Ezzi
Nov 15 at 6:57
I'd suggest do the sum of column I in one query, then wrap another query around it, filtering out the summed rows that do not meet the condition. Something like =QUERY(QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1), "where Col2I >= "&T3&"")
– JPV
Nov 15 at 8:08
|
show 1 more comment
up vote
0
down vote
Try
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND I >= "&S3&" AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
answered Nov 12 at 12:29
JPV
10.3k21425
Hey! Your solution worked like a charm! Thanks a million!!! Please help me solve the other query as well.
– Huzaifa Ezzi
Nov 12 at 16:21
Hi! Sorry, but I did not test your solution before posting the comment. There seems to be an error. It only filters the rows which consist of values greater than what I enter in 'S3' whereas I need it to filter the summed up quantity. Here is the link to the sheet. Please copy the data and see if you can get a solution. Thank you.
– Huzaifa Ezzi
Nov 12 at 17:48
@HuzaifaEzzi: Ok. so you want the summed values in I to be >= than S3? Can"t access your spreadsheet, please fix permissions.
– JPV
Nov 13 at 8:14
Yes, that is correct. Please find the query in cell 'Q7' of from the Farmers' Interactive Summary sheet of this file. . Thank you.
– Huzaifa Ezzi
Nov 15 at 6:57
I'd suggest do the sum of column I in one query, then wrap another query around it, filtering out the summed rows that do not meet the condition. Something like =QUERY(QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1), "where Col2I >= "&T3&"")
– JPV
Nov 15 at 8:08
|
show 1 more comment
up vote
0
down vote
up vote
0
down vote
Try
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND I >= "&S3&" AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
answered Nov 12 at 12:29
JPV
10.3k21425
Try
=QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND I >= "&S3&" AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1)
answered Nov 12 at 12:29
JPV
10.3k21425
answered Nov 12 at 12:29
JPV
10.3k21425
answered Nov 12 at 12:29
JPV
10.3k21425
answered Nov 12 at 12:29
JPV
10.3k21425
10.3k21425
Hey! Your solution worked like a charm! Thanks a million!!! Please help me solve the other query as well.
– Huzaifa Ezzi
Nov 12 at 16:21
Hi! Sorry, but I did not test your solution before posting the comment. There seems to be an error. It only filters the rows which consist of values greater than what I enter in 'S3' whereas I need it to filter the summed up quantity. Here is the link to the sheet. Please copy the data and see if you can get a solution. Thank you.
– Huzaifa Ezzi
Nov 12 at 17:48
@HuzaifaEzzi: Ok. so you want the summed values in I to be >= than S3? Can"t access your spreadsheet, please fix permissions.
– JPV
Nov 13 at 8:14
Yes, that is correct. Please find the query in cell 'Q7' of from the Farmers' Interactive Summary sheet of this file. . Thank you.
– Huzaifa Ezzi
Nov 15 at 6:57
I'd suggest do the sum of column I in one query, then wrap another query around it, filtering out the summed rows that do not meet the condition. Something like =QUERY(QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1), "where Col2I >= "&T3&"")
– JPV
Nov 15 at 8:08
|
show 1 more comment
Hey! Your solution worked like a charm! Thanks a million!!! Please help me solve the other query as well.
– Huzaifa Ezzi
Nov 12 at 16:21
Hi! Sorry, but I did not test your solution before posting the comment. There seems to be an error. It only filters the rows which consist of values greater than what I enter in 'S3' whereas I need it to filter the summed up quantity. Here is the link to the sheet. Please copy the data and see if you can get a solution. Thank you.
– Huzaifa Ezzi
Nov 12 at 17:48
@HuzaifaEzzi: Ok. so you want the summed values in I to be >= than S3? Can"t access your spreadsheet, please fix permissions.
– JPV
Nov 13 at 8:14
Yes, that is correct. Please find the query in cell 'Q7' of from the Farmers' Interactive Summary sheet of this file. . Thank you.
– Huzaifa Ezzi
Nov 15 at 6:57
I'd suggest do the sum of column I in one query, then wrap another query around it, filtering out the summed rows that do not meet the condition. Something like =QUERY(QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1), "where Col2I >= "&T3&"")
– JPV
Nov 15 at 8:08
Hey! Your solution worked like a charm! Thanks a million!!! Please help me solve the other query as well.
– Huzaifa Ezzi
Nov 12 at 16:21
Hey! Your solution worked like a charm! Thanks a million!!! Please help me solve the other query as well.
– Huzaifa Ezzi
Nov 12 at 16:21
Hi! Sorry, but I did not test your solution before posting the comment. There seems to be an error. It only filters the rows which consist of values greater than what I enter in 'S3' whereas I need it to filter the summed up quantity. Here is the link to the sheet. Please copy the data and see if you can get a solution. Thank you.
– Huzaifa Ezzi
Nov 12 at 17:48
Hi! Sorry, but I did not test your solution before posting the comment. There seems to be an error. It only filters the rows which consist of values greater than what I enter in 'S3' whereas I need it to filter the summed up quantity. Here is the link to the sheet. Please copy the data and see if you can get a solution. Thank you.
– Huzaifa Ezzi
Nov 12 at 17:48
@HuzaifaEzzi: Ok. so you want the summed values in I to be >= than S3? Can"t access your spreadsheet, please fix permissions.
– JPV
Nov 13 at 8:14
@HuzaifaEzzi: Ok. so you want the summed values in I to be >= than S3? Can"t access your spreadsheet, please fix permissions.
– JPV
Nov 13 at 8:14
Yes, that is correct. Please find the query in cell 'Q7' of from the Farmers' Interactive Summary sheet of this file. . Thank you.
– Huzaifa Ezzi
Nov 15 at 6:57
Yes, that is correct. Please find the query in cell 'Q7' of from the Farmers' Interactive Summary sheet of this file. . Thank you.
– Huzaifa Ezzi
Nov 15 at 6:57
I'd suggest do the sum of column I in one query, then wrap another query around it, filtering out the summed rows that do not meet the condition. Something like =QUERY(QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1), "where Col2I >= "&T3&"")
– JPV
Nov 15 at 8:08
I'd suggest do the sum of column I in one query, then wrap another query around it, filtering out the summed rows that do not meet the condition. Something like =QUERY(QUERY(Data!B:T,"select F, sum(I), sum(T) where D is not null AND E contains '"&Q2&"' AND G contains '"&S2&"' AND B contains '"&Q4&"' group by F order by sum(I) desc label sum(I) 'الكمية', sum(T) 'القيمة'",1), "where Col2I >= "&T3&"")
– JPV
Nov 15 at 8:08
|
show 1 more comment
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53247060%2fneed-google-query-to-only-show-sum-of-items-greater-than-value-from-cell-and-ano%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Maybe post a slightly modified version of the question, with the arabic characters replaced by English. A link to a test sheet with a minimal problem will also help to get attention.
– bcperth
Nov 11 at 22:32
I'll try to answer both the question in one reply. Firstly, lets use the English term 'quantity' for 'الكمية' and 'value' for 'القيمة' to clear things a bit. 'Quantity' falls under column 'I' and 'value' under 'N.' Now to answer @JPV's question, your solution will only give me quantities greater than that is 'S3' without summing up column 'I' whereas I'm looking for accumulated quantity and not individually sold.
– Huzaifa Ezzi
Nov 12 at 10:49