# Thread: Summing multiple criteria across rows and columns Thanks: 0 Likes: 0

1. ## Summing multiple criteria across rows and columns

I need to be able to solve the following problem without arrays or sum product solution. Would be grateful to anyone that can help.

If have a set of data with columns:
1 3 5 2 1 5 2 3

And rows:
Dog
Cat
Fish
Dog
Dog
Cat

and there is data in each cell in this matrix.

I need to sum the values where the column and row values match (i.e. sum all values where column = 1 and row = dog).

2. ## Re: Summing multiple criteria across rows and columns

hi - welcome to the board.

Imagine your data is in b1:h8:

=SUM(IF(C1:H1=1,IF(B2:B8="dog",C2:H8)))

..entered with control + shift + enter, not just enter.

3. ## Re: Summing multiple criteria across rows and columns

Sorry - I have just seen your 'no arrays or sumproducts' restriction.

In which case:

Do it long hand (i.e. once for each row in the table) then add up the results.

4. ## Re: Summing multiple criteria across rows and columns

Thank you for the reply, PaddyD. However, I don't want to do an array - the performance is poor on large data sets. Also, I want to have something that is dynamic. I have one formula that uses sumif, offset and match, but only aggregates multiple row headers.

5. ## Re: Summing multiple criteria across rows and columns

"..the performance is poor on large data sets. Also, I want to have something that is dynamic."

Provide more details about the real world scenario you are working with.

6. ## Re: Summing multiple criteria across rows and columns

 Animal Feb March Feb cats 8 23 1 dogs 9 12 2 cats 2 5 6 birds 2 4 4 dogs 5 8 3

SUMIFS(OFFSET(\$B\$3:\$B\$7,0,MATCH(\$C\$2:\$E\$2,C\$9,0),),\$B\$3:\$B\$7,\$B10) This formula will only aggregate row headers

SUMPRODUCT((\$B\$3:\$B\$7=\$B12)*(\$C\$2:\$E\$2=C\$9)*(\$C\$3:\$E\$7)) This formula woks but I do not want to us an array

Any suggestion?

7. ## Re: Summing multiple criteria across rows and columns

Is this what you want?

=SUMIF(B3:B7,B12,INDEX(C3:E7,0,MATCH(C9,C2:E2,FALSE)))

8. ## Re: Summing multiple criteria across rows and columns

Originally Posted by Andrew Poulsom
Is this what you want?

=SUMIF(B3:B7,B12,INDEX(C3:E7,0,MATCH(C9,C2:E2,FALSE)))

Unfortunately, no. That formula is similar to my first example. It only sums the animal that matches the first matched period (feb)- it does not sum the values that intersect both Feb periods like the array example does. Any other suggestions?

9. ## Re: Summing multiple criteria across rows and columns

=SUM(INDEX((C3:E7)*(B3:B7=B12)*(C2:E2=C9),))

10. ## Re: Summing multiple criteria across rows and columns

I assume that is an array? I want to avoid using arrays due to performance. Any other suggestions?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•