Summing multiple criteria across rows and columns

kimberly01

New Member
Joined
Sep 30, 2013
Messages
5
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).
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
"..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.
 
Upvote 0
AnimalFebMarchFeb
cats8231
dogs9122
cats256
birds244
dogs583
<colgroup><col width="64" style="width: 48pt;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="3"> <tbody> </tbody>


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?


Thanks for your help!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top