title: Using Max function result in SumIFS Excel
body: <p><a href="https://i.stack.imgur.com/Etm63.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Etm63.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p>The answer for apples for 8 th month would be 14 assuming there is another record as <br>Apples - 32 - 10 - 8.</p>
  
  <p>So I would have to find max week number for a given month and then sum the quantity for Apples with max week number </p>
  
  <p>The formula written is 
  =SUMIFS( C2:C300, A2:A300, H16, B2:B300, MAX(IF(D2:D300=MONTH(TODAY()-1),B2:B300)))</p>
  
  <p>H16 contains the word "Apples"</p>
  
  <p>Column A - Item<br>
  Column B - Week<br>
  Column C - Quantity<br>
  Column D - Month<br>
  Column H - Unique Items again..<br></p>
  
  <p>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. <br>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.</p>