SUMIFS using 3 criteria
Hi...I need help with a formula, I need to sum a value by lane based on a monthly date. I feel I have the date portion correct but not getting the sum part? I need the front end of the formula...
AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2021))
Best Answer
-
Try this:
=SUMIFS({Range to Sum}, {Lane Range}, @cell = 1, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2021))
Answers
-
@John Littler You want to add numbers to a running sum if the month of Estimated equals 4 and the year of Estimated equals 2021? The result is held elsewhere, in a different sheet or separate column?
Dale
-
@DMurphy I am looking to sum all in the "total projected" that meet the criteria of "lane" number and month. For example sum all in lane 0 for May, all for lane 1 in May...etc.
-
Try this:
=SUMIFS({Range to Sum}, {Lane Range}, @cell = 1, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2021))
-
Thanks again Paul...worked like a charm! Appreciate you helping me build my solutions!
-
The only possible refinement to the answer from @Paul Newcome would be to point to a variable in your (MONTH(@cell), 0) = 4 statement, replacing "4" with a pointer. (same with the year). And do so in a separate sheet to build a metric view ...
Dale
-
@DMurphy That is what I personally do, but I try not to do that in examples here to avoid confusion and make it just a little more obvious which parts determine the year and month we are counting/summing for.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!