SUMIF with Row AND Column Criteria

[Slazar]

Welcome everyone! This is my first post here. I hope to become an Excel master in due time.
A B C D
1 Oranges Apples Banana

2 Red 3 5 7

3 Blue 5 3 6

4 Red 2 1 1

5 Blue 5 5 5

Can somebody explain to me how to sum the numbers as to get all types of fruits in only one colored basket?
I need a draggable formula.

I'm having a lot of trouble trying to get to this despite looking in the forums.

A B C D
1 Oranges Apples Banana

2 Red [Formula here]

3 Blue

 

[dreid1011]

Try this:

=SUMPRODUCT(($B$2:$D$5)*--($A$2:$A$5=$A7)*--($B$1:$D$1=B$6))

Change the following ranges to suit your data:
B2:D5 is the sum range, A2:A5 is the colors, A7 is the color in your summary table, B1:D1 is the fruit, B6 is the fruit in your summary table.

Enter in B2 in your summary table, copy across and down.

 

[Aladin Akyurek]

Try this:

=SUMPRODUCT(($B$2:$D$5)*--($A$2:$A$5=$A7)*--($B$1:$D$1=B$6))

Change the following ranges to suit your data:
B2:D5 is the sum range, A2:A5 is the colors, A7 is the color in your summary table, B1:D1 is the fruit, B6 is the fruit in your summary table.

Enter in B2 in your summary table, copy across and down.

This *-- is not illegal, but it does not make sense. You should use either only * or replace your *-- usage with ,--, that is, comma followed by double minus...

 

[Aladin Akyurek]

@Slazar

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		Oranges	Apples	Banana				Oranges	Apples	Banana
2​	Red	3​	5​	7​			Red	5​	6​	8​
3​	Blue	5​	3​	6​			Blue	10​	8​	11​
4​	Red	2​	1​	1​
5​	Blue	5​	5​	5​

H2, copied across and down:

=SUMIFS(INDEX($B$2:$D$5,0,MATCH(H$1,$B$1:$D$1,0)),$A$2:$A$5,$G2)

which is fast.

More generic and dynamic...

H2, copied across and down:

=SUMIFS(INDEX($B:$D,0,MATCH(H$1,INDEX($B:$D,1,0),0)),$A:$A,$G2)

 

[Slazar]

Thank you! It worked!

Quick question. How do you insert a snapshot of the Excel sheet or perhaps make a table like Aladin's?

 

[dreid1011]

This *-- is not illegal, but it does not make sense. You should use either only * or replace your *-- usage with ,--, that is, comma followed by double minus...

When I do this, ',--', the formula returns #VALUE. Otherwise, the formula works fine when I use '*--'.

 

[Aladin Akyurek]

Thank you! It worked!

Hope you are referring to SUMIFS...

Quick question. How do you insert a snapshot of the Excel sheet or perhaps make a table like Aladin's?

Download and install this add-in: https://app.box.com/s/soezox25h3w0q5s4rcyl.

 

[Aladin Akyurek]

When I do this, ',--', the formula returns #VALUE. Otherwise, the formula works fine when I use '*--'.

You have to use the * operator if you want to process a matrix range (B2:D5 is such) in a SumProduct formula, that is:

=SUMPRODUCT(($B$2:$D$5)*($A$2:$A$5=$A7)*($B$1:$D$1=B$6))

But, if a SUMIFS formula is appropriate, one would go for that. If a matrix range must be processed, better switch to an more appropriate array-processing formula:

=SUM(IF($A$2:$A$5=$A7,IF($B$1:$D$1=B$6,$B$2:$D$5)))

Hope this helps.