SUMIFS using 3 criteria

John Littler

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

2021-05-06_14-21-26.jpg

Uploaded May 6, 202166.15 KB

AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2021))

@Paul Newcome @Sue Hill @Kimberly Loveless

Paul Newcome

Try this:

=SUMIFS({Range to Sum}, {Lane Range}, @cell = 1, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2021))

Dale Murphy

@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

John Littler

@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.

Paul Newcome

Try this:

=SUMIFS({Range to Sum}, {Lane Range}, @cell = 1, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2021))

John Littler

Thanks again Paul...worked like a charm! Appreciate you helping me build my solutions!

Dale Murphy

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

Paul Newcome

@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.