Forum Discussion

Kia Boon Goh's avatar
Kia Boon Goh
Copper Contributor
Aug 24, 2018

Sumifs - area

Hi all,

 

i have the following data and i was trying to use the sumifs formula based on the criteria store in D18 and D19. However the sumifs formula unable to work in this way. Appreciate if there is any solution to solve this issue.

 

 

excel

2 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Aug 24, 2018

    Hi,

     

    In this case, you have to call INDEX & MATCH functions along with SUMIF as follows:

    =SUMIF($C$3:$C$14,D18,INDEX($D$3:$G$14,,MATCH(D19,$D$2:$G$2,0)))

    This is because you have a grid of sales items, and you have to provide SUMIF with the column you want it to look into.

    You can determine that column based on the criteria in cell D19 by using INDEX & MATCH combination.

     

    I hope that helps

    Regards

    • Kia Boon Goh's avatar
      Kia Boon Goh
      Copper Contributor
      Aug 24, 2018

      Thanks! the formula is working!

       

      have a nice day :D