Sum based on multiple conditions

**NMJones** · 08-28-2009, 12:43 PM

I am trying to sum numbers based on three counditions. My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*

The yellow cell contains the formula I am using.

**NBVC** · 08-28-2009, 12:46 PM

Try:

=SUM(IF((A1:A10="South")*(C1:C10="Meat")*(LEFT(E1:E10,2)="bd"),D1:D10))

or

=SUMPRODUCT(--(A1:A10="South"),--(C1:C10="Meat"),--(LEFT(E1:E10,2)="bd"),D1:D10)

which doesn't need to be confirmed with CSE key combination.

**NMJones** · 08-28-2009, 12:59 PM

The first returned #VALUE!

The second returned 0

**NBVC** · 08-28-2009, 01:01 PM

The first needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER...

In your sample data there are no items in E1:E10 that begin with "bd".. so you get 0.

**NMJones** · 08-28-2009, 01:07 PM

E2 and E4 both start with bd

I confirmed the first formula and returned 0

OH - forgot it was also filtering for "South" and "Meat"

It's working

Thanks