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:

[TABLE="width: 500"]
[TR]
[TD]Product[/TD]
[TD]Supplier[/TD]
[TD]Qty[/TD]
[TD]Code[/TD]
[TD][/TD]
[TD]Supplier1[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]Cherries[/TD]
[TD]John[/TD]
[TD]250[/TD]
[TD]508.4[/TD]
[TD]250[/TD]
[TD]Supplier2[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]Sally[/TD]
[TD]200[/TD]
[TD]511.4[/TD]
[TD][/TD]
[TD]Supplier3[/TD]
[TD]Pete[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]Pete[/TD]
[TD]180[/TD]
[TD]512.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]Mike[/TD]
[TD]400[/TD]
[TD]508.4[/TD]
[TD]400[/TD]
[TD]Code1[/TD]
[TD]508.4[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]Sally[/TD]
[TD]250[/TD]
[TD]511.4[/TD]
[TD][/TD]
[TD]Code2[/TD]
[TD]511.4[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]Mike[/TD]
[TD]120[/TD]
[TD]512.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cherries[/TD]
[TD]Sally[/TD]
[TD]300[/TD]
[TD]508.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]Pete[/TD]
[TD]110[/TD]
[TD]511.4[/TD]
[TD]110[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Amt[/TD]
[TD]760[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Product​

[/TD]
[TD]

Supplier​

[/TD]
[TD]

Qty​

[/TD]
[TD]

Code​

[/TD]
[TD][/TD]
[TD]

Supplier1​

[/TD]
[TD="bgcolor: #DCE6F1"]

Mike​

[/TD]
[TD][/TD]
[TD]

Total Code1​

[/TD]
[TD]

Total Codes 1 and 2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Cherries​

[/TD]
[TD]

John​

[/TD]
[TD]

250​

[/TD]
[TD]

508.4​

[/TD]
[TD]

250​

[/TD]
[TD]

Supplier2​

[/TD]
[TD="bgcolor: #DCE6F1"]

John​

[/TD]
[TD][/TD]
[TD]

650​

[/TD]
[TD]

760​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

Sally​

[/TD]
[TD]

200​

[/TD]
[TD]

511.4​

[/TD]
[TD][/TD]
[TD]

Supplier3​

[/TD]
[TD="bgcolor: #DCE6F1"]

Pete​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Apples​

[/TD]
[TD]

Pete​

[/TD]
[TD]

180​

[/TD]
[TD]

512.4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

Mike​

[/TD]
[TD]

400​

[/TD]
[TD]

508.4​

[/TD]
[TD]

400​

[/TD]
[TD]

Code1​

[/TD]
[TD]

Code2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

Sally​

[/TD]
[TD]

250​

[/TD]
[TD]

511.4​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

508.4​

[/TD]
[TD="bgcolor: #D9D9D9"]

511.4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Apples​

[/TD]
[TD]

Mike​

[/TD]
[TD]

120​

[/TD]
[TD]

512.4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Cherries​

[/TD]
[TD]

Sally​

[/TD]
[TD]

300​

[/TD]
[TD]

508.4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Apples​

[/TD]
[TD]

Pete​

[/TD]
[TD]

110​

[/TD]
[TD]

511.4​

[/TD]
[TD]

110​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Amt​

[/TD]
[TD]

760​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Product​

[/TD]
[TD]

Supplier​

[/TD]
[TD]

Qty​

[/TD]
[TD]

Code​

[/TD]
[TD][/TD]
[TD]

Supplier1​

[/TD]
[TD]

Mike​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Cherries​

[/TD]
[TD]

John​

[/TD]
[TD]

250​

[/TD]
[TD]

508.4​

[/TD]
[TD]

250​

[/TD]
[TD]

Supplier2​

[/TD]
[TD]

John​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

Sally​

[/TD]
[TD]

200​

[/TD]
[TD]

511.4​

[/TD]
[TD][/TD]
[TD]

Supplier3​

[/TD]
[TD]

Pete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Apples​

[/TD]
[TD]

Pete​

[/TD]
[TD]

180​

[/TD]
[TD]

512.4​

[/TD]
[TD][/TD]
[TD]

Code1​

[/TD]
[TD]

508.4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

Mike​

[/TD]
[TD]

400​

[/TD]
[TD]

508.4​

[/TD]
[TD]

400​

[/TD]
[TD]

Code2​

[/TD]
[TD]

511.4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

Sally​

[/TD]
[TD]

250​

[/TD]
[TD]

511.4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Apples​

[/TD]
[TD]

Mike​

[/TD]
[TD]

120​

[/TD]
[TD]

512.4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Cherries​

[/TD]
[TD]

Sally​

[/TD]
[TD]

300​

[/TD]
[TD]

508.4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Apples​

[/TD]
[TD]

Pete​

[/TD]
[TD]

110​

[/TD]
[TD]

511.4​

[/TD]
[TD]

110​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Amt​

[/TD]
[TD]

760​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

508.4​

[/TD]
[TD]

511.4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

Mike​

[/TD]
[TD]

508.4​

[/TD]
[TD="bgcolor: #D9D9D9"]

400​

[/TD]
[TD][/TD]
[TD]

Mike​

[/TD]
[TD="bgcolor: #DCE6F1"]

400​

[/TD]
[TD="bgcolor: #DCE6F1"]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

John​

[/TD]
[TD]

511.4​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD][/TD]
[TD]

John​

[/TD]
[TD="bgcolor: #DCE6F1"]

250​

[/TD]
[TD="bgcolor: #DCE6F1"]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

Pete​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD][/TD]
[TD]

Pete​

[/TD]
[TD="bgcolor: #DCE6F1"]

0​

[/TD]
[TD="bgcolor: #DCE6F1"]

110​

[/TD]
[/TR]
</tbody>[/TABLE]


- 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.