Setting 'OR' Criteria on SUMIF

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
Good morning all

I have entered the following array formula which at first glance appears to work. However it is not picking up the first set of criteria (=SD4600 or =SD4650) instead it is summing all based on the remaining criteria?

=SUM(IF(OR(Aptos!$F$7:$F$500="SD4600",Aptos!$F$7:$F$500="SD4650"),IF(Aptos!$E$7:$E$500="Spend to Period",IF(Aptos!$C$7:$C$500='Central MMR'!$A10,Aptos!$J$7:$J$500))))

Initially I had these as two SUMIF formulas added together (Sum(if…. + Sum(if….)) which works fine but is a bit scary looking and thought there might be a way to incorporate my criteria ‘double’ multiple criteria for column F.

Additionally would I need to replace ‘=’ with ‘<>’ to get the reverse? i.e. = anything except SD4600 & SD4650 as I need to sum on similar criteria for all cost centres except SD4600 & SD4650 and I am not sure how to do this otherwise

Cheers
Julian
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=SUM(IF(ISNUMBER(MATCH(Aptos!$F$7:$F$500,{"SD4600","SD4650"},0)),IF(Aptos!$E$7:$E$500="Spend to Period",IF(Aptos!$C$7:$C$500='Central MMR'!$A10,Aptos!$J$7:$J$500))))

which must be confirmed with control+shift+enter.

Alternatively:

=SUMPRODUCT(--ISNUMBER(MATCH(Aptos!$F$7:$F$500,{"SD4600","SD4650"},0)),--(Aptos!$E$7:$E$500="Spend to Period"),--(Aptos!$C$7:$C$500='Central MMR'!$A10),Aptos!$J$7:$J$500)

which needs just enter.
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Try,

=SUMPRODUCT(--(ISNUMBER(MATCH(Aptos!$F$7:$F$500,{"SD4600","SD4650"},0))),--(Aptos!$E$7:$E$500="Spend to Period"),--(Aptos!$C$7:$C$500='Central MMR'!$A10),Aptos!$J$7:$J$500)


=SUMPRODUCT(--(ISNA(MATCH(Aptos!$F$7:$F$500,{"SD4600","SD4650"},0))),--(Aptos!$E$7:$E$500="Spend to Period"),--(Aptos!$C$7:$C$500='Central MMR'!$A10),Aptos!$J$7:$J$500)

HTH
 

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
Dear Aladin & Krishnakumar

Thank you for your posts. I have a choice!!!
I have some new formula to investigate/learn.

Only on question thought

How do i do the same but to sum all in 'F' except SD4600 and SD4650?

Cheers
Julian
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615

ADVERTISEMENT

Hi Julian,

See my second formula.

HTH
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Dear Aladin & Krishnakumar

Thank you for your posts. I have a choice!!!
I have some new formula to investigate/learn.

Only on question thought

How do i do the same but to sum all in 'F' except SD4600 and SD4650?

Cheers
Julian

=SUMPRODUCT(--ISNA(MATCH(Aptos!$F$7:$F$500,{"SD4600","SD4650"},0)),--(Aptos!$E$7:$E$500="Spend to Period"),--(Aptos!$C$7:$C$500='Central MMR'!$A10),Aptos!$J$7:$J$500)

If you have blanks in F7:F500, the foregoing formula will include them. If you rather would exclude them...

=SUMPRODUCT(--ISNA(MATCH(Aptos!$F$7:$F$500,{"SD4600","SD4650"},0)),--(Aptos!$F$7:$F$500<>""),--(Aptos!$E$7:$E$500="Spend to Period"),--(Aptos!$C$7:$C$500='Central MMR'!$A10),Aptos!$J$7:$J$500)
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569

ADVERTISEMENT

Good morning Barefoot

I have some new formula to investigate/learn.

My add-in available for download via the link below allows you to contruct the type of formulae that Aladin and Krishnakumar have outlined above (using SumProduct to SUM or COUNT cells) through selecting the cells to add (/count) and building up the specification with up to six conditions. As you are building the formula a window at the bottom of the dialog shows the formula so far - just to help you understand how it's built up.

HTH

DominicB
 

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
Dear Krishnakumar

Oops!!! Egg on face!!

Sorry and again thanks for you help the formula(s) are working great.

On playing around I see you can refer to a range instead of ...{"SD4600","SD4650"}... but I seem to have hit a brick wall in trying to use a wildcard e.g. "SD48*"
Is this possible?


Cheers
Julian
 

Barefoot

Board Regular
Joined
Sep 20, 2005
Messages
87
Hi Dominic

Thank you,

Yes I came across your website the other day and have downloaded your addin.
I just haven’t loaded it yet as I haven’t had the time to look at it. I am waiting on a new PC today and once I have that I’ll be loading it up!!

I am really looking forward to seeing/using it.

Oh hell I can't wait now!!!

Cheers
Julian
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

=SUMPRODUCT(--ISNUMBER(SEARCH("SD48",Aptos!$F$7:$F$500)),--(Aptos!$E$7:$E$500="Spend to Period"),--(Aptos!$C$7:$C$500='Central MMR'!$A10),Aptos!$J$7:$J$500)

HTH
 

Forum statistics

Threads
1,141,294
Messages
5,705,537
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top