Issue with SumIFS

DSullivan

Hi,

I am trying to get the sum of Green Communities eligible projects in a fiscal Year. What I have for a formula is:

=SUMIFS([Fiscal Year]:[Fiscal Year], "FY23", [Estimated Acquisition Cost]:[Estimated Acquisition Cost], >0, [Grant Name]:[Grant Name], "Green Communities")

And this is throwing a Incorrect Argument set.

Any ideas what I am missing?

Thanks!!

Jeff Reisman

Hi Jeff,<\/p>

Thanks for the quick answer. I'm trying to sum the Estimated Acquisition Cost field. <\/p>

The criteria I'm trying to use is Grant Name = Green Communities and Fiscal Year = FY23<\/p>

Thanks again!<\/p>

Dave<\/p>","bodyRaw":"[{\"insert\":\"Hi Jeff,\\nThanks for the quick answer. I'm trying to sum the Estimated Acquisition Cost field. \\nThe criteria I'm trying to use is Grant Name = Green Communities and Fiscal Year = FY23\\n\\nThanks again!\\n\\nDave\\n\"}]","format":"rich","dateInserted":"2022-01-24T21:01:49+00:00","insertUser":{"userID":139402,"name":"DSullivan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/DSullivan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/A6G6O8AX71SP\/nWT0T184PUMDZ.png","dateLastActive":"2023-03-24T14:14:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/317187#Comment_317187","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/317187#Comment_317187

So in that case you list Estimated Acquisition Cost as your range (without criteria,) before the criteria ranges. It's okay to have a criteria for the range you are finding the sum for, you just have to list the range a second time in the formula paired with the criteria.

=SUMIFS([Estimated Acquisition Cost]:[Estimated Acquisition Cost], [Fiscal Year]:[Fiscal Year], "FY23", [Estimated Acquisition Cost]:[Estimated Acquisition Cost], >0, [Grant Name]:[Grant Name], "Green Communities")

Jeff Reisman

Which field are you trying to find the sum for? In your formula, you give it three sets of criteria ranges and criteria, but don't indicate which range it should sum. List the range to be summed first, followed by you range/criteria sets. (It's okay if the range to sum is one of those listed with criteria later in the formula.)

=SUMIFS([Range to Sum]:[Range to Sum], [Fiscal Year]:[Fiscal Year], "FY23", [Estimated Acquisition Cost]:[Estimated Acquisition Cost], >0, [Grant Name]:[Grant Name], "Green Communities")

DSullivan

Hi Jeff,

Thanks for the quick answer. I'm trying to sum the Estimated Acquisition Cost field.

The criteria I'm trying to use is Grant Name = Green Communities and Fiscal Year = FY23

Thanks again!

Dave

Jeff Reisman

Hi Jeff,<\/p>

Thanks for the quick answer. I'm trying to sum the Estimated Acquisition Cost field. <\/p>

The criteria I'm trying to use is Grant Name = Green Communities and Fiscal Year = FY23<\/p>

Thanks again!<\/p>

Dave<\/p>","bodyRaw":"[{\"insert\":\"Hi Jeff,\\nThanks for the quick answer. I'm trying to sum the Estimated Acquisition Cost field. \\nThe criteria I'm trying to use is Grant Name = Green Communities and Fiscal Year = FY23\\n\\nThanks again!\\n\\nDave\\n\"}]","format":"rich","dateInserted":"2022-01-24T21:01:49+00:00","insertUser":{"userID":139402,"name":"DSullivan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/DSullivan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/A6G6O8AX71SP\/nWT0T184PUMDZ.png","dateLastActive":"2023-03-24T14:14:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/317187#Comment_317187","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/317187#Comment_317187

So in that case you list Estimated Acquisition Cost as your range (without criteria,) before the criteria ranges. It's okay to have a criteria for the range you are finding the sum for, you just have to list the range a second time in the formula paired with the criteria.

=SUMIFS([Estimated Acquisition Cost]:[Estimated Acquisition Cost], [Fiscal Year]:[Fiscal Year], "FY23", [Estimated Acquisition Cost]:[Estimated Acquisition Cost], >0, [Grant Name]:[Grant Name], "Green Communities")

DSullivan

Thanks again for your assistance! That worked!!