Using Excel sumifs to sum matrix elements

excel sumifs
danas.zuokas picture danas.zuokas · May 14, 2012 · Viewed 23k times · Source

Is it possible using Excel's sumifs to find the sum of elements of a matrix using condition on row and column? For example matrix:

    c   d   e
a   1   2   3
b   4   5   6

I would like to sum elements of b row, d and e columns. The solution should work also for large dimension matrix. Sorry for ugly formatting.

Answer

lori_m picture lori_m · May 14, 2012

Maybe this formula could be extended to your needs: (data in A1:D3)

=SUMPRODUCT(--ISNUMBER(MATCH(B1:D1,{"d","e"},0)*MATCH(A2:A3,{"b"},0)),B2:D3)

Related questions

Excel SUMIF between dates

I have column A with date values formatted as mm/dd/yyyy. I am trying to sum the values of column B if A >=DATE(2012,1,1) AND =SUM(B:B) sums B properly, but if I try to use =SUMIF(…

Read More
Using SUMIFS with multiple AND OR conditions

I would like to create a succinct Excel formula that SUMS a column based on a set of AND conditions, plus a set of OR conditions. My Excel table contains the following data and I used defined names for the …

Read More
Excel VBA code for SumIF function

Hi I am writing VBA code such that - If the value of cell M1 is found in any cell Value of Range D1:D20 then Find the sum of corresponding cell values in Range C1:C20. But I am …

Read More