Google sheet SUMIF's fixed range issue
up vote
1
down vote
favorite
I am trying to apply the formula for sumif's based on a fixed range, and I cannot get results.
Screenshots of issue:
https://ibb.co/dZygvL- https://ibb.co/ng4X20
I am required level 10 reputation to be able to post images so sorry for the inconvenience. I just included links for images.
Formula:
=sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
Does anyone know why this formula is not returning a sum?
google-sheets sumifs
edited Nov 19 at 10:44
Rbar
95011134
asked Nov 19 at 4:33
Waqas Ahmad
62
|
show 2 more comments
up vote
1
down vote
favorite
I am trying to apply the formula for sumif's based on a fixed range, and I cannot get results.
Screenshots of issue:
https://ibb.co/dZygvL- https://ibb.co/ng4X20
I am required level 10 reputation to be able to post images so sorry for the inconvenience. I just included links for images.
Formula:
=sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
Does anyone know why this formula is not returning a sum?
google-sheets sumifs
edited Nov 19 at 10:44
Rbar
95011134
asked Nov 19 at 4:33
Waqas Ahmad
62
formula is not visible in images above so i am including formula down here in comments : =sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
– Waqas Ahmad
Nov 19 at 4:34
I dont see any problem with the function. Do you have any extra characters in cells with text "done" or "Waqas"? Is it that the hours column doesnt have proper numbers? Try checking it with a smaller set such as d1:d5 instead of d1:d100?
– shahkalpesh
Nov 19 at 4:47
nope i don't have any extra characters the values i have cross checked they are already binded as drop down lists to have fixed values for columns C and E and i have also tried by narrowing down the range it still doesn't work but if sum of range function is working fine for bigger range sumif's shouldn't it work too with bigger range even though i have already checked no the format of the data types in columns and everything seems to be in order
– Waqas Ahmad
Nov 19 at 4:51
You may try copying the value of "Done" and "Waqas" from C2 and E2 respectively, into formula than to type it. See if that changes anything?
– shahkalpesh
Nov 19 at 4:57
have tried doing that as well but no luck below is shareable link for the sheet and please find the sheet by the title of testing.... docs.google.com/spreadsheets/d/…
– Waqas Ahmad
Nov 19 at 6:17
|
show 2 more comments
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am trying to apply the formula for sumif's based on a fixed range, and I cannot get results.
Screenshots of issue:
https://ibb.co/dZygvL- https://ibb.co/ng4X20
I am required level 10 reputation to be able to post images so sorry for the inconvenience. I just included links for images.
Formula:
=sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
Does anyone know why this formula is not returning a sum?
google-sheets sumifs
edited Nov 19 at 10:44
Rbar
95011134
asked Nov 19 at 4:33
Waqas Ahmad
62
I am trying to apply the formula for sumif's based on a fixed range, and I cannot get results.
Screenshots of issue:
https://ibb.co/dZygvL- https://ibb.co/ng4X20
I am required level 10 reputation to be able to post images so sorry for the inconvenience. I just included links for images.
Formula:
=sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
Does anyone know why this formula is not returning a sum?
google-sheets sumifs
google-sheets sumifs
edited Nov 19 at 10:44
Rbar
95011134
asked Nov 19 at 4:33
Waqas Ahmad
62
edited Nov 19 at 10:44
Rbar
95011134
asked Nov 19 at 4:33
Waqas Ahmad
62
edited Nov 19 at 10:44
Rbar
95011134
edited Nov 19 at 10:44
Rbar
95011134
edited Nov 19 at 10:44
Rbar
95011134
95011134
asked Nov 19 at 4:33
Waqas Ahmad
62
asked Nov 19 at 4:33
Waqas Ahmad
62
asked Nov 19 at 4:33
Waqas Ahmad
62
62
formula is not visible in images above so i am including formula down here in comments : =sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
– Waqas Ahmad
Nov 19 at 4:34
I dont see any problem with the function. Do you have any extra characters in cells with text "done" or "Waqas"? Is it that the hours column doesnt have proper numbers? Try checking it with a smaller set such as d1:d5 instead of d1:d100?
– shahkalpesh
Nov 19 at 4:47
nope i don't have any extra characters the values i have cross checked they are already binded as drop down lists to have fixed values for columns C and E and i have also tried by narrowing down the range it still doesn't work but if sum of range function is working fine for bigger range sumif's shouldn't it work too with bigger range even though i have already checked no the format of the data types in columns and everything seems to be in order
– Waqas Ahmad
Nov 19 at 4:51
You may try copying the value of "Done" and "Waqas" from C2 and E2 respectively, into formula than to type it. See if that changes anything?
– shahkalpesh
Nov 19 at 4:57
have tried doing that as well but no luck below is shareable link for the sheet and please find the sheet by the title of testing.... docs.google.com/spreadsheets/d/…
– Waqas Ahmad
Nov 19 at 6:17
|
show 2 more comments
formula is not visible in images above so i am including formula down here in comments : =sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
– Waqas Ahmad
Nov 19 at 4:34
I dont see any problem with the function. Do you have any extra characters in cells with text "done" or "Waqas"? Is it that the hours column doesnt have proper numbers? Try checking it with a smaller set such as d1:d5 instead of d1:d100?
– shahkalpesh
Nov 19 at 4:47
nope i don't have any extra characters the values i have cross checked they are already binded as drop down lists to have fixed values for columns C and E and i have also tried by narrowing down the range it still doesn't work but if sum of range function is working fine for bigger range sumif's shouldn't it work too with bigger range even though i have already checked no the format of the data types in columns and everything seems to be in order
– Waqas Ahmad
Nov 19 at 4:51
You may try copying the value of "Done" and "Waqas" from C2 and E2 respectively, into formula than to type it. See if that changes anything?
– shahkalpesh
Nov 19 at 4:57
have tried doing that as well but no luck below is shareable link for the sheet and please find the sheet by the title of testing.... docs.google.com/spreadsheets/d/…
– Waqas Ahmad
Nov 19 at 6:17
formula is not visible in images above so i am including formula down here in comments : =sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
– Waqas Ahmad
Nov 19 at 4:34
formula is not visible in images above so i am including formula down here in comments : =sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
– Waqas Ahmad
Nov 19 at 4:34
I dont see any problem with the function. Do you have any extra characters in cells with text "done" or "Waqas"? Is it that the hours column doesnt have proper numbers? Try checking it with a smaller set such as d1:d5 instead of d1:d100?
– shahkalpesh
Nov 19 at 4:47
I dont see any problem with the function. Do you have any extra characters in cells with text "done" or "Waqas"? Is it that the hours column doesnt have proper numbers? Try checking it with a smaller set such as d1:d5 instead of d1:d100?
– shahkalpesh
Nov 19 at 4:47
nope i don't have any extra characters the values i have cross checked they are already binded as drop down lists to have fixed values for columns C and E and i have also tried by narrowing down the range it still doesn't work but if sum of range function is working fine for bigger range sumif's shouldn't it work too with bigger range even though i have already checked no the format of the data types in columns and everything seems to be in order
– Waqas Ahmad
Nov 19 at 4:51
nope i don't have any extra characters the values i have cross checked they are already binded as drop down lists to have fixed values for columns C and E and i have also tried by narrowing down the range it still doesn't work but if sum of range function is working fine for bigger range sumif's shouldn't it work too with bigger range even though i have already checked no the format of the data types in columns and everything seems to be in order
– Waqas Ahmad
Nov 19 at 4:51
You may try copying the value of "Done" and "Waqas" from C2 and E2 respectively, into formula than to type it. See if that changes anything?
– shahkalpesh
Nov 19 at 4:57
You may try copying the value of "Done" and "Waqas" from C2 and E2 respectively, into formula than to type it. See if that changes anything?
– shahkalpesh
Nov 19 at 4:57
have tried doing that as well but no luck below is shareable link for the sheet and please find the sheet by the title of testing.... docs.google.com/spreadsheets/d/…
– Waqas Ahmad
Nov 19 at 6:17
have tried doing that as well but no luck below is shareable link for the sheet and please find the sheet by the title of testing.... docs.google.com/spreadsheets/d/…
– Waqas Ahmad
Nov 19 at 6:17
|
show 2 more comments
1 Answer
1
active
oldest
votes
up vote
0
down vote
The "problem" formula actually works just fine. But it has a flaw: it can't be easily copied down the column to apply to other workers. A generic version of the formula is =sumifs($D$2:$D , $C$2:$C , "Done", $E$2:$E , F5) where F5 is the name of the worker. Note, this also provides for infinite column lengths.
Another option is that the OP could list the names of workers by using =sort(unique(E2:E)). While the OP may need a longer list for other purposes, this shouldn't stop using a function to pickup names from a master list.
answered Dec 2 at 19:26
Tedinoz
5711516
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
The "problem" formula actually works just fine. But it has a flaw: it can't be easily copied down the column to apply to other workers. A generic version of the formula is =sumifs($D$2:$D , $C$2:$C , "Done", $E$2:$E , F5) where F5 is the name of the worker. Note, this also provides for infinite column lengths.
Another option is that the OP could list the names of workers by using =sort(unique(E2:E)). While the OP may need a longer list for other purposes, this shouldn't stop using a function to pickup names from a master list.
answered Dec 2 at 19:26
Tedinoz
5711516
add a comment |
up vote
0
down vote
The "problem" formula actually works just fine. But it has a flaw: it can't be easily copied down the column to apply to other workers. A generic version of the formula is =sumifs($D$2:$D , $C$2:$C , "Done", $E$2:$E , F5) where F5 is the name of the worker. Note, this also provides for infinite column lengths.
Another option is that the OP could list the names of workers by using =sort(unique(E2:E)). While the OP may need a longer list for other purposes, this shouldn't stop using a function to pickup names from a master list.
answered Dec 2 at 19:26
Tedinoz
5711516
add a comment |
up vote
0
down vote
up vote
0
down vote
The "problem" formula actually works just fine. But it has a flaw: it can't be easily copied down the column to apply to other workers. A generic version of the formula is =sumifs($D$2:$D , $C$2:$C , "Done", $E$2:$E , F5) where F5 is the name of the worker. Note, this also provides for infinite column lengths.
Another option is that the OP could list the names of workers by using =sort(unique(E2:E)). While the OP may need a longer list for other purposes, this shouldn't stop using a function to pickup names from a master list.
answered Dec 2 at 19:26
Tedinoz
5711516
The "problem" formula actually works just fine. But it has a flaw: it can't be easily copied down the column to apply to other workers. A generic version of the formula is =sumifs($D$2:$D , $C$2:$C , "Done", $E$2:$E , F5) where F5 is the name of the worker. Note, this also provides for infinite column lengths.
Another option is that the OP could list the names of workers by using =sort(unique(E2:E)). While the OP may need a longer list for other purposes, this shouldn't stop using a function to pickup names from a master list.
answered Dec 2 at 19:26
Tedinoz
5711516
answered Dec 2 at 19:26
Tedinoz
5711516
answered Dec 2 at 19:26
Tedinoz
5711516
answered Dec 2 at 19:26
Tedinoz
5711516
5711516
add a comment |
add a comment |
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.
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%2f53368363%2fgoogle-sheet-sumifs-fixed-range-issue%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
formula is not visible in images above so i am including formula down here in comments : =sumifs($D$2:$D$1000 , $C$2:$C$1000 , "Done", $E$2:$E$1000 , "Waqas")
– Waqas Ahmad
Nov 19 at 4:34
I dont see any problem with the function. Do you have any extra characters in cells with text "done" or "Waqas"? Is it that the hours column doesnt have proper numbers? Try checking it with a smaller set such as d1:d5 instead of d1:d100?
– shahkalpesh
Nov 19 at 4:47
nope i don't have any extra characters the values i have cross checked they are already binded as drop down lists to have fixed values for columns C and E and i have also tried by narrowing down the range it still doesn't work but if sum of range function is working fine for bigger range sumif's shouldn't it work too with bigger range even though i have already checked no the format of the data types in columns and everything seems to be in order
– Waqas Ahmad
Nov 19 at 4:51
You may try copying the value of "Done" and "Waqas" from C2 and E2 respectively, into formula than to type it. See if that changes anything?
– shahkalpesh
Nov 19 at 4:57
have tried doing that as well but no luck below is shareable link for the sheet and please find the sheet by the title of testing.... docs.google.com/spreadsheets/d/…
– Waqas Ahmad
Nov 19 at 6:17