Excel 2007 SUMIFS

C

C3PU10

Hello
How can I use OR operator in the SUMIFS function?
I have 4 columns:

Value Pay Period Supplier Type
100 9 Local L
200 9 Local K
300 10 US L

I want to sum all the values of: suppliers = local, period = 9 and
type = (L or K)
I just don't know how to approach the "L or K" problem...

Thanks,
 
R

Roger Govier

Hi

I don't think you can use an OR within SUMIFS.
Try
=SUMPRODUCT((B2:B4=9)*(C2:C4="Local")*(D2:D4={"L","K"})*(A2:A4))

If you want to use SUMIFS, then add a helper column with
=IF(OR(D2="K",D2="L"),1,0)
then use
=SUMIFS(A2:A4,B2:B4,9,C2:C4,"Local",E2:E4,1)
 
C

C3PU10

Thank you very much!
This is much better then my:
=SUMIFS(A2:A4,B2:B4,9,C2:C4,"Local",D2:D4,"L")
+SUMIFS(A2:A4,B2:B4,9,C2:C4,"Local",D2:D4,"K")

:)
 
Top