Using Max function result in SumIFS Excel

Question

I have a table like the image given above. There is another sheet which reports some information based on this data. I need the sum of Apples in the last week for a given month.

The answer for apples for 8 th month would be 14 assuming there is another record as
Apples - 32 - 10 - 8.

So I would have to find max week number for a given month and then sum the quantity for Apples with max week number

The formula written is =SUMIFS( C2:C300, A2:A300, H16, B2:B300, MAX(IF(D2:D300=MONTH(TODAY()-1),B2:B300)))

H16 contains the word "Apples"

Column A - Item
Column B - Week
Column C - Quantity
Column D - Month
Column H - Unique Items again..

MAX(IF(D2:D300=MONTH(TODAY()-1),B2:B300)) returns the correct max as 32 for month 8 but the formula returns the value as 0.
If I put the max formula in aseperate cell and refer that cell in main formula, it works as expected. I am not sure why the result of max value is not being used for sumif function.

Answer 1

Your formula is good. all you need to do is to convert it into Array Formula. Just type the formula with Ctr + shift + enter.

{=SUMIFS( C2:C300, A2:A300, H16, B2:B300, MAX(IF(D2:D300=MONTH(TODAY()-1),B2:B300)))}

Actually, without array, the part IF(D2:D300 evaluates to #value error as Excel doesn't not know how to compare an array against one value.

Hint: Not sureif you know it already, but anyways, always use Formulas --> Evaluate Formula to pin-point an error in a formula.