Multiple Criteria In Multiple Columns

[EdStockton]

Hi Folks,

I want to be able to get a sum for a column using multiple criteria in at least two other columns. I don't have a problem when using multiple criteria in only one problem, but I can't find a way to have multiple criteria in multiple columns.

An example follows:

Product	Supplier	Qty	Code		Supplier1	Mike
Cherries	John	250	508.4	250	Supplier2	John
Bananas	Sally	200	511.4		Supplier3	Pete
Apples	Pete	180	512.4
Oranges	Mike	400	508.4	400	Code1	508.4
Bananas	Sally	250	511.4		Code2	511.4
Apples	Mike	120	512.4
Cherries	Sally	300	508.4
Apples	Pete	110	511.4	110
			Amt	760

I know how to create a formula that will consider multiple criteria in one column as follows:

=sumproduct(sumifs(C1:C9,B1:B9,G1:G3,D1:D9,G4)) = 650

However, when I try to use multiple criteria in two columns, as follows, I do not get the correct answer.

=sumproduct(sumifs(C1:C9,B1:B9,G1:G3,D1:D9,G4:G5)) = 400 However, the correct answer should be 760.

I have looked extensively on line and the only alternative I have found that works is to use two separate formulas that are then added together. It seems there should be a way to make this work.

Thanks, Ed Stockton

 

[pkdeimos]

Hi,

The following array formula worked -

=SUMPRODUCT(C2:C9, IF(IFERROR(MATCH(B2:B9,G1:G3,0),0),1,0), IF(IFERROR(MATCH(D2:D9,G4:G5,0),0),1,0))

Condition 1 to match supplier
Condition 2 to match code

The formula you used may not be an appropriate way to use condition on multiple columns with multiple criteria.
In the case of
=sumproduct(sumifs(C1:C9,B1:B9,G1:G3,D1:D9,G4:G5)) = 400

Conditions applied are (Mike and 508.4), (John and 511.4), (Pete and null) which returned 400,0,0 and in turn a total sum of 400.

Hope this helps.
Cheers.

 

[Marcelo Branco]

To use two array-criteria one must be a vertical range (or array) and the other a horizontal range (or array)

Something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Product​	Supplier​	Qty​	Code​		Supplier1​	Mike​		Total Code1​	Total Codes 1 and 2​
2​	Cherries​	John​	250​	508.4​	250​	Supplier2​	John​		650​	760​
3​	Bananas​	Sally​	200​	511.4​		Supplier3​	Pete​
4​	Apples​	Pete​	180​	512.4​
5​	Oranges​	Mike​	400​	508.4​	400​	Code1​	Code2​
6​	Bananas​	Sally​	250​	511.4​		508.4​	511.4​
7​	Apples​	Mike​	120​	512.4​
8​	Cherries​	Sally​	300​	508.4​
9​	Apples​	Pete​	110​	511.4​	110​
10​
11​				Amt​	760​

<tbody>
</tbody>

Observe the criteria
Crit1 = vertical range G1:G3
Crit2 = horizontal range F6:G6

Formula in I2
=SUMPRODUCT(SUMIFS(C1:C9,B1:B9,G1:G3,D1:D9,F6))

Formula in J2
=SUMPRODUCT(SUMIFS(C1:C9,B1:B9,G1:G3,D1:D9,F6:G6))

M.

 

[pkdeimos]

To use two array-criteria one must be a vertical range (or array) and the other a horizontal range (or array)

Crit2 = horizontal range F6:G6

This is really interesting.
Now this got me wondering if it works when there is an additional condition?

 

[Marcelo Branco]

This is really interesting.
Now this got me wondering if it works when there is an additional condition?

With more than two conditions you should use SUMPRODUCT with ISNUMBER(MATCH(range,criteria_range,0)) for each criteria

For example, considering the data sample in post #1 with two vertical criteria, G1:G3 and G4:G5, this should work
=SUMPRODUCT(--ISNUMBER(MATCH(B2:B9,G1:G3,0)),--ISNUMBER(MATCH(D2:D9,G4:G5,0)),C2:C9)

M.

 

[EdStockton]

pkdeimos and Marcelo Branco, all of your solutions worked great. Thank you so much for your help.

Marcelo, your final solution is one that I thought I tried but could not get it to work. Obviously I did something wrong.

Again, thank you both so much for your help. Mr. Excel has the best participants for solving problems.

 

[Marcelo Branco]

You are very welcome. Glad to help

M.

 

[EdStockton]

Marcelo Branco, I have a question for you. In your first example, why does it make a difference that the second set of criteria is horizontal as opposed to vertical? That makes no sense to me.

 

[Marcelo Branco]

Marcelo Branco, I have a question for you. In your first example, why does it make a difference that the second set of criteria is horizontal as opposed to vertical? That makes no sense to me.

See if this answer your question

	A​	B​	C​	D​	E​	F​	G ​
1​	Product​	Supplier​	Qty​	Code​		Supplier1​	Mike​
2​	Cherries​	John​	250​	508.4​	250​	Supplier2​	John​
3​	Bananas​	Sally​	200​	511.4​		Supplier3​	Pete​
4​	Apples​	Pete​	180​	512.4​		Code1​	508.4​
5​	Oranges​	Mike​	400​	508.4​	400​	Code2​	511.4​
6​	Bananas​	Sally​	250​	511.4​
7​	Apples​	Mike​	120​	512.4​
8​	Cherries​	Sally​	300​	508.4​
9​	Apples​	Pete​	110​	511.4​	110​
10​
11​				Amt​	760​
12​
13​
14​						508.4​	511.4​
15​	Mike​	508.4​	400​		Mike​	400​	0​
16​	John​	511.4​	0​		John​	250​	0​
17​	Pete​		0​		Pete​	0​	110​

<tbody>
</tbody>

- Using 2 vertical ranges (A15:A17 and B15:B16)
Formula in C15 copied down (gray area)
=SUMIFS($C$2:$C$9,$B$2:$B$9,A15,$D$2:$D$9,B15)

If you add SUMPRODUCT and use the full criteria ranges
=SUMPRODUCT(SUMIFS(C2:C9,B2:B9,A15:A17,D2:D9,B15:B16))
it returns 400 (wrong result)


- With a vertical criteria E15:E17 and a horizontal criteria F14:G14
Formula in F15 copied across and down (blue area)
=SUMIFS($C$2:$C$9,$B$2:$B$9,$E15,$D$2:$D$9,F$14)

and
=SUMPRODUCT(SUMIFS(C2:C9,B2:B9,E15:E17,D2:D9,F14:G14))
returns 760 (correct result!)

Hope this clarifies the issue

M.

 

[EdStockton]

Thank you much. You have been very helpful.