-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Need help with Sumifs and or Combination...excel formula:
- Thread starter pedie
- Start date
barry houdini
MrExcel MVP
- Joined
- Mar 23, 2005
- Messages
- 20,825
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"}))
=SUM(SUMIFS($D$1:$D$27,$B$1:$B$27,"May",$C$1:$C$27,{"FEC","ORANGE"}))
Upvote
0
Marcelo Branco
MrExcel MVP
- Joined
- Aug 23, 2010
- Messages
- 17,103
- Office Version
- 2021
- 2010
- Platform
- Windows
Hi,
Maybe
=SUMPRODUCT(--(B1:B27="May"), --(((C1:C27="FEC")+(C1:C27="ORANGE"))>0),D1:D27)
HTH
M.
Maybe
=SUMPRODUCT(--(B1:B27="May"), --(((C1:C27="FEC")+(C1:C27="ORANGE"))>0),D1:D27)
HTH
M.
Last edited:
Upvote
0
Marcelo Branco
MrExcel MVP
- Joined
- Aug 23, 2010
- Messages
- 17,103
- Office Version
- 2021
- 2010
- Platform
- Windows
Great! thanks alot. Both works perfect!
Thanks again for the solution!
You are welcome.
Tks for the feedback
M.
Upvote
0
Marcelo Branco
MrExcel MVP
- Joined
- Aug 23, 2010
- Messages
- 17,103
- Office Version
- 2021
- 2010
- Platform
- Windows
In my formula add this condition
--(((A1:A27="NY")+(A1:A27="LA")+(A1:A27)="GA"))>0)
M.
--(((A1:A27="NY")+(A1:A27="LA")+(A1:A27)="GA"))>0)
M.
Upvote
0
barry houdini
MrExcel MVP
- Joined
- Mar 23, 2005
- Messages
- 20,825
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).
=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).
Upvote
0
pedie
Well-known Member
- Joined
- Apr 28, 2010
- Messages
- 3,875
'=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.
Barcelo, thanks....I tried this way and it error, how exactly do i organise this formula?
Thanks again for helping.
Upvote
0
Similar threads
- Question
- Question
