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

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.