Search code examples
arraysexcelsumifs

sumifs + offset function

I´m stuck trying to build this excel function:

I have this matrix (see picture) with quarter year sums and I want it to sum up to the month I indicate in the cell next to "Month", excluding all the quarteryear sums. So far my Formula, as an array: 

={SUM(SUMIFS(($Q20:OFFSET($Q20;0;$S$16-1));R19:AG19;AD16:AG16))}

Any ideas?

Thanks in advance!

Solution

  • Assuming your Data is in A1:Q2 like this

    Then you could either use this in Q2:

    =SUM(A2:P2)/2

    if you only want to calculate the sum for the whole year, or like this, if you also watn to use the formula during the year:

    =SUMPRODUCT(A2:P2,--(MOD(COLUMN($A:$P),4)<>0))

    (Auf deutsch dann so:

    =SUMMENPRODUKT(A2:P2;--(REST(SPALTE($A:$P);4)<>0))

    )