I am trying to use sum ifs another sheet column is between dates and equal "spm"

Sean Fritz

=SUMIFS({Sofia}, ="SPM", {Sofia (Shawn) 2021-22 YTD},AND({Sof}>=(2021,8,1),{Sof},<=(2021,8,31),{Sofia (Shawn) 2021-22 YTD}))

Kelly Moore

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) 2021-22 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) 2021-22 YTD},{Sofia}, ="SPM", {Sof}, YEAR(@cell)=2021, {Sof}, MONTH(@cell)=8)

Does this get you what you needed?

Kelly Moore

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) 2021-22 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) 2021-22 YTD},{Sofia}, ="SPM", {Sof}, YEAR(@cell)=2021, {Sof}, MONTH(@cell)=8)

Does this get you what you needed?

Sean Fritz

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))

Kelly Moore

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