Want to use an OR criteria in a sumifs function

  • Thread starter Thread starter eggman2001
  • Start date Start date
E

eggman2001

Hello,

I'd like to something like:
SUMIFS(data!A:A, data!B:B, "January" or "February")

So that it sets the first critera as either "January" or "February".
Can someone tell me how I can do this?
 
eggman2001 formulated the question :
Hello,

I'd like to something like:
SUMIFS(data!A:A, data!B:B, "January" or "February")

So that it sets the first critera as either "January" or "February".
Can someone tell me how I can do this?
Click to expand...

I don't think you can do that. Try...

=SUMIF(A:A,"January",B:B)+SUMIF(A:A,"February",B:B)
 
You could try:
=sum(SUMIF(A:A,{"January","February"},B:B))

It doesn't look like you had to use =sumifs() in the original formula, either:

=sumif(a:a,"January",b:b)
may have been sufficient, but I'm not sure I know what you're doing...
 
Dave Peterson submitted this idea :
You could try:
=sum(SUMIF(A:A,{"January","February"},B:B))
Click to expand...

I tried this at first but found it only uses the first criteria,
ignoring anything following. Got the same result entering as an array
formula.
 
I'd try it again. Maybe you didn't type the curly-brackets correctly? Or
misspelled one of the months in the criteria range???

It doesn't have to be array entered, either.
 
Dave Peterson laid this down on his screen :
I'd try it again. Maybe you didn't type the curly-brackets correctly? Or
misspelled one of the months in the criteria range???

It doesn't have to be array entered, either.
Click to expand...

Thanks for persisting! Actually, I omitted wrapping SUMIF() in the
SUM() function. -duh!
 
Thanks very much Dave.
what I'm trying to do is similar, but instead of using set values (ie "January" or "February"), I would like to refer to the content of cells, something like:

sum(sumifs(A:A,B:B,{&F1,&F2},C:C,criteria)

I tried with and without the quotes around &F1, but it does not work.
It works fine if I replace &F1 and &F2 by the values in these cells. However, I have 100's of formulas so it's not very practical to change them everytime.

So far, I simply use
sumifs(A:A,B:B,"="&F1,C:C,criteria)+sumifs(A:A,B:B,"="&F2,criteria)
This works, but it's not very flexible, especially if I want to add one more value (ie have to change 100's of formulas)
 
Hello pif sydney,
I looked on internet to do the exact same thing as you. Did you find any solution ?

"what I'm trying to do is similar, but instead of using set values (ie "January" or "February"), I would like to refer to the content of cells, something like:

sum(sumifs(A:A,B:B,{&F1,&F2},C:C,criteria)"

Thx,
FC9029
 
Ok, here is the solution :

With criteria B in F1, F2, F3, F4
Criteria C in H1

{=sum(sumifs(A:A,B:B,F1:F4,C:C,H1)}
{} --> (ctrl + shift + enter)
 
Hi, I have also been working on a similar problem, and tried the same sort of syntax as you. For my particular spreadsheet, this syntax finally worked.

{=SUM(SUMIF($H$4:$H$13,A5:F5,$I$4:$I$13))}

Hope his helps.
Rob

FC9023 said:
Hello pif sydney,
I looked on internet to do the exact same thing as you. Did you find any solution ?

"what I'm trying to do is similar, but instead of using set values (ie "January" or "February"), I would like to refer to the content of cells, something like:

sum(sumifs(A:A,B:B,{&F1,&F2},C:C,criteria)"

Thx,
FC9029
Click to expand...
 
I honestly would not have been able to figure this out, without testing out what I short of knew with what was suggested earlier on this thread. I've actually tested this and to verify it does work:

SUMIFS Formula with multiple conditions and one OR option using {}:


=SUM(SUMIFS(C:C,F:F,{"Criteria1","Criteria2"},S:S,"Criteria3"))


So replace C with the column you want to get your count from (your sum range)
Replace F with your first criteria range, followed by criteria
Replace S with your second criteria range, followed by criteria

Note placing criteria 1 & 2 in the {} acts like an OR statement.

This formula sums column C where column S=Criteria3 AND column F= Criteria1 OR Criteria2.

Works perfectly... and I've never posted to forums before- but I figured I'd give back what I gained because I so appreciated the missing link to this key.

Thanks and I hope this will be useful to someone! :D
 
Hi
I don't normally post in forums as generally find someone else has already asked/answered my questions but I just can't solve/find the answer to this one so would appreciate any help you can give!

I am trying to do similar to the previous posters except I need it to return the sum value for all possible combinations of the multiple values entered as criteria.
E.g. below is the example data set ~(real data set is hundreds of lines long and using pivots is NOT an option as I need to the sum the data up different ways on different tabs. Data tab is a download from BW and on one page, Projects that need to pull from the BW data are on other tabs. in additon to below data & criteria I have year and month in my real data.)
A/1___ B ___________C ___________D __________E
2 ____Material ______Vendor______ Plant_______Spend
3 ____123___________ 234 _________1_________100
4 ____123 ____________234________ 2 ________150
5 ____321 ____________234________ 2 ________110
6 ____123 ____________245 ________1________ 120
7 ____321 ____________111________2 ________130
8 ____321 ____________111 ________1 ________101
9 ____123 ____________111 ________1 ________100
10 ____321____________ 124 ________2 ________110
11 ____543 ____________259 ________1 ________120
12 ____543 ____________234 ________1 ________130
13
14
15
16 Criteria
17 ____Plant___________1
18 ___________________2
19 ____Vendor ________234
20 __________________111
21 ____Material _______123
22 __________________321

I want to use a SUMIFS formula to return the spend for all combinations of Material 123 or 234 and vendor 234 or 111 and plant 1 or 2.
When I try the formula
=SUMPRODUCT(SUMIFS($E$2:$E$12,$B$2:$B$12,C21:C22,$C$2:$C$12,C19:C20,$D$2:$D$12,C17:C18))

I only get the result 230. That is the red bold rows (3+7). But I want it to return the results for ALL the red rows (which should be 691).

I tried with an OR formula in the criteria part but can't get it to work. I saw some posts about the { } to simulate the OR funcation but can't get it to work for the multiple OR criteria I have. I can get it to work if I have 2 vendors and one material and one plant.. But not if there are 2 vendors & 2 materials & 2 plants (so 6 possible combinations of the variables) Can anyone get it to work without having to do multiple sumifs statements ?. I currenlty have it set up with multiple sumifs but it would be much more userfiendly if I could get it work as I want so it is easier for the end user of the workbook to use and maintain as new project tabs (and hence look up formulas) are added to the file.
 
Last edited:
Hi,

For this to work correctly, you need to build up the different combination in the referance tables like this.
Plant
1
2
1
2
1
2
1
2
Vendor*
111
111
234
234
111
111
234
234
Material*
123
123
123
123
321
321
321
321
This way the formula should calculate the number 691
 
Back
Top