I am trying to use sum ifs another sheet column is between dates and equal "spm"
=SUMIFS({Sofia}, ="SPM", {Sofia (Shawn) 202122 YTD},AND({Sof}>=(2021,8,1),{Sof},<=(2021,8,31),{Sofia (Shawn) 202122 YTD}))
Best Answer

Hi @Sean Fritz
The SUMIFS function has the syntax SUMIFS(range (that you're summing), range1, criteria1, range2, criteria2, etc). The SUMIFs function can be used regardless of the number of criteria  from 1 to infinity. Also, any terms used in the SUMIFs are automatically AND terms.
I think this is what you intended.
=SUMIFS({Sofia (Shawn) 202122 YTD},{Sofia}, ="SPM", {Sof}, @cell>=DATE(2021,8,1),{Sof}, @cell<=DATE(2021,8,31))
Since you are looking for a month's worth of data, one could use the MONTH function (maybe with the YEAR function if multiple years exist in your data), if you are having to update the formula monthly. Note the numbers are not in quotes as we want them to behave as numbers, not as text characters.
=SUMIFS({Sofia (Shawn) 202122 YTD},{Sofia}, ="SPM", {Sof}, YEAR(@cell)=2021, {Sof}, MONTH(@cell)=8)
Does this get you what you needed?
Answers

Hi @Sean Fritz
The SUMIFS function has the syntax SUMIFS(range (that you're summing), range1, criteria1, range2, criteria2, etc). The SUMIFs function can be used regardless of the number of criteria  from 1 to infinity. Also, any terms used in the SUMIFs are automatically AND terms.
I think this is what you intended.
=SUMIFS({Sofia (Shawn) 202122 YTD},{Sofia}, ="SPM", {Sof}, @cell>=DATE(2021,8,1),{Sof}, @cell<=DATE(2021,8,31))
Since you are looking for a month's worth of data, one could use the MONTH function (maybe with the YEAR function if multiple years exist in your data), if you are having to update the formula monthly. Note the numbers are not in quotes as we want them to behave as numbers, not as text characters.
=SUMIFS({Sofia (Shawn) 202122 YTD},{Sofia}, ="SPM", {Sof}, YEAR(@cell)=2021, {Sof}, MONTH(@cell)=8)
Does this get you what you needed?

Thank you, I did use the month function and it works. I would like the zero value to be blank for the dashboard graph. Is this the best way to accomplish that
=IF(SUMIFS({Chris Z TS}, {Chris Z Intervention}, ="SPD Major", {Chris Z Date}, YEAR(@cell) = 2021, {Chris Z Date}, MONTH(@cell) = 11, {Chris Z TS}, >0) = 0, "", SUMIFS({Chris Z TS}, {Chris Z Intervention}, ="SPD Major", {Chris Z Date}, YEAR(@cell) = 2021, {Chris Z Date}, MONTH(@cell) = 11, {Chris Z TS}, >0))

I'm a big believer if the formula works, (without being crazy complicated), it's a good formula, so this is a good formula. Without seeing your data layout and knowing what is behind the ranges listed above, I cannot offer a better way. If it's working, kudos to you and go for it. 🏆
Kelly
Help Article Resources
Categories
Check out the Formula Handbook template!