5

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.

ChrisWue
  • 18,612
  • 4
  • 58
  • 83
danas.zuokas
  • 4,551
  • 4
  • 29
  • 39

2 Answers2

8

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)
lori_m
  • 5,487
  • 1
  • 18
  • 29
  • Wow. That was really quick. And if rows and columns are identified by numbers? Say 1, 2 instead of a, b and 1, 2, 3 instead of c, d, e. How to write a condition "sum where row name equals 2 and column name is less than 3"? – danas.zuokas May 14 '12 at 11:55
  • 2
    It should be easier with numbers, try something like: `=SUMPRODUCT((B1:D1<3)*(A2:A3=2),B2:D3)` – lori_m May 14 '12 at 12:01
0

Along similar lines, you can try:

=SUM(((B1:D1="d")+(B1:D1="e"))*(A2:A3="b")*B2:D3)

which uses the idea that with respect to the arithmetic operations, TRUE is 1 and FALSE is 0.

user3716832
  • 111
  • 3