Need help with Sumifs and or Combination...excel formula:

[pedie]

Hi, current formula:
'=SUMIFS($D$1:$D$27,$B$1:$B$27,"May",$C$1:$C$27,OR("FEC","ORANGE"))

I want to sum col D if col c either has "FEC" or "Orange"
and month is May in Col B.

Thanks for the Advice.

 

[barry houdini]

Assuming column B has the months as text (not formatted dates) try

=SUM(SUMIFS($D$1:$D$27,$B$1:$B$27,"May",$C$1:$C$27,{"FEC","ORANGE"}))

 

[Marcelo Branco]

Hi,

Maybe

=SUMPRODUCT(--(B1:B27="May"), --(((C1:C27="FEC")+(C1:C27="ORANGE"))>0),D1:D27)

HTH

M.

 

[pedie]

Great! thanks alot. Both works perfect!
Thanks again for the solution!

 

[Marcelo Branco]

Great! thanks alot. Both works perfect!
Thanks again for the solution!

You are welcome.

Tks for the feedback

M.

 

[pedie]

Sorry to bring this up again but I want to know how to achieve this along with the current formula...
if col A = or("NY","LA","GA")

Can this condition be added to current formula?

Thanks again guys!

 

[Marcelo Branco]

In my formula add this condition

--(((A1:A27="NY")+(A1:A27="LA")+(A1:A27)="GA"))>0)

M.

 

[barry houdini]

Within my suggestion you can do this

=SUM(SUMIFS($D$1:$D$27,$B$1:$B$27,"May",$C$1:$C$27,{"FEC","ORANGE"},$A$1:$A$27,{"NY";"LA";"GA"}))

Note that with two "array constants" one has to use comma separators, one has to use semi-colons. If you wanted a third then that would be problematic using that syntax - another approach which is more easily extendable to large numbers of "OR"s is to use SUMPRODUCT with ISNUMBER(MATCH, i.e.

=SUMPRODUCT($D$1:$D$27,($B$1:$B$27="May")*ISNUMBER(MATCH($C$1:$C$27,{"FEC","ORANGE"},0)*MATCH($A$1:$A$27,{"NY","LA","GA"},0)))

In that setup the separators can be commas or semi-colons, it doesn't matter which (as long as you are consistent within each part).

 

[pedie]

'=SUMPRODUCT(--(B1:B27="May"),--(((C1:C27="FEC")+(C1:C27="ORANGE"))>0),--(((A1:A27="NY")+(A1:A27="LA")+(A1:A27)="GA"))>0),D1:D27)

Barcelo, thanks....I tried this way and it error, how exactly do i organise this formula?
Thanks again for helping.

 

[pedie]

Sorry, i think it has to be this way...
'=SUMPRODUCT(--(B1:B27="May"),--(((C1:C27="FEC")+(C1:C27="ORANGE"))>0),--(((A1:A27="NY")+(A1:A27="LA")+(A1:A27="GA"))>0),D1:D27)
I think misplaced the brackets...