Setting 'OR' Criteria on SUMIF

B

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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
=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
Click to expand...

=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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

OvernightCellebrity
  • Solved
SUMIF slowing calculation times
2
Replies
12
Views
440
kvsrinivasamurthy
K
E
  • Question Question
Sumifs, Index, formula only summing first value
Replies
1
Views
696
etaf
etaf
excelbytes
  • Solved
SUMIFS With Multiple Criteria In One Column
2 3
Replies
26
Views
9K
KRice
K
R
  • Question Question
Sumifs with nested Xlookup-- Adding Indirect to perform calculation across multiple sheets
Replies
1
Views
644
Eric W
Eric W
P
  • Question Question
Sumif Column Criteria Issue
Replies
3
Views
255
NickFru
N

Forum statistics

Threads
1,223,832
Messages
6,174,905
Members
452,590
Latest member
CraiginColorado

Share this page

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