Excel 2007 SUMIFS

[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,

 

[Roger Govier]

Hi

I don't think you can use an OR within SUMIFS.
Try
=SUMPRODUCT((B2:B4=9)*(C2:C4="Local")*(D24={"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)

 

[C3PU10]

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