CODEWITHSUNDEEP
excelsum
Camille Westlake
Camille Westlake

Reputation: 11

Excel SumIF with range as Column and Sum range as ROW

I am trying to make this work: 

=SUMIF(MATRIX!$B$2:$B$36,"YES",B5:AJ5)

Note that the range is a COLUMN and the sum range is a ROW but when the formula computes it doesn't sum the Row B5:AJ5 it actually sums B5:B40. What do I need to add to have it sum the ROW and not the COLUMN.

EXAMPLE:

enter image description here

Upvotes: 1

Views: 3689

Answers (1)

user4039065
user4039065

Reputation:

As you have discovered, a SUMIF expects both the criteria array and the sum array to be both rows or columns but not one of each. You have correctly used the same number of cells in each; the problem is that they are in different directions. A TRANSPOSE function can reverse the direction that the outer function 'sees' the one of the arrays but you need to change from SUMIF to SUMPRODUCT and enter it as an array formula with Ctrl+Shift+Enter.

=SUMPRODUCT((B$2:B$5="yes")*(TRANSPOSE($H2:$K2)))

When entered correctly with CSE, the result in L2 is 2.3. Fill both right and down for something resembling the following.

enter image description here

I don't believe you can use transpose with SUMIF but someone might know a trick to it.

Upvotes: 1

Related Questions