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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
=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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,022
Messages
6,163,458
Members
451,837
Latest member
gmc

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
Back
Top