SumIf with more than one condition

**qwyz** · 05-12-2009, 08:00 AM

Hi, I'm trying create a Sum(If formula with 2 conditions, but whatever I try doesn't seem to want to cooperate. I want to add up the sum of all invoices according to the country they deal with and the month that they were created in. I've attached a sample file showing what I'm trying to accomplish.
Here is how far I've gotten so far:
=SUM(IF(Main!$L:$L;B21*Main!$Z:$Z;C20);Main!$H:$H)

I've tried other forms as well but even though this only returns #Value!, at least the formula is accepted.

Any help would be greatly appreciated!

**JONvdHeyden** · 05-12-2009, 08:04 AM

This applies to the sample that you provided.

In cell J5:

=SUMPRODUCT(--($D$5:$D$21=$J$4),--($E$5:$E$21=$I5),$C$5:$C$21)

**zbor** · 05-12-2009, 08:05 AM

Try this:

=SUMPRODUCT(--($E$5:$E$21=$I5);--($D$5:$D$21=J$4);$C$5:$C$21)

**DonkeyOte** · 05-12-2009, 08:52 AM

If your intention is create a matrix I would advise 2 alternative approaches to SUMPRODUCT... namely (in order of preference)

1. Pivot Table (country as Row Field, Month as Column Field and Amount as Data Field)

2. Concatenation & SUMIF, ie

F5: =$E5&":"&$D5
copy down

J5: =SUMIF($F$5:$F$21,$I5&":"&J$4,$C$5:$C$21)
copy down

SUMPRODUCTS when used in large quantities will affect (adversely) the performance of your file, a Pivot Table is invariably the best solution available... failing that using Concatenation and a standard SUMIF approach will be more efficient if less-elegant... pending volumes of data in play the difference in performance can be immense.