Search code examples
excelindexingmatchsumifs

Excel Sumifs with index match

I'm trying to write a formula that gives me the following output:

enter image description here

The information is gathered from here:

enter image description here

The formula I have used is =SUMIFS(INDEX(Planning!$AU$493:$FF$497,,MATCH('Resource overview'!AY1,Planning!AU1:FF1,0)),Planning!$G$493:$G$497,'Resource overview'!AY2)

But this gives me the following output:

enter image description here

This is wrong, I want to be able to SUM the amounts for the entire row where e.g. App and 2021-06-01 occurs, so in this case I want to get 11 937.5 (2387.5+2387.5+2387.5+2387.5+2387.5)

How can I tweak the code to get the desired result?

Solution 
  • =SUMIFS(OFFSET(Planning!$AU$493:$FF$493,MATCH('Resource overview'!AY$2,Planning!$G$493:$G$497,0)-1,0),Planning!$AU$1:$FF$1,'Resource overview'!AY$1)

    should do..

    Idea : "shift" the sumifs range with offset & match (text match). The sumifs criteria defines the date match.

    p/s : somehow the trick work for countif/countblank .. but it doesn't work for sumif

    Please share if it works/not.