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

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.