Want to use an OR criteria in a sumifs function

[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?

 

[GS]

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?

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

=SUMIF(A:A,"January",B:B)+SUMIF(A:A,"February",B:B)

 

[Dave Peterson]

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...

 

[GS]

Dave Peterson submitted this idea :

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

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.

 

[Dave Peterson]

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.

 

[GS]

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.

Thanks for persisting! Actually, I omitted wrapping SUMIF() in the
SUM() function. -duh!

 

[Dave Peterson]

Whew!

Dave Peterson laid this down on his screen :

Thanks for persisting! Actually, I omitted wrapping SUMIF() in the SUM()
function. -duh!

 

[GS]

Dave Peterson wrote :

Whew!

That's what I said too!<g>

 

[pif sydney]

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)

 

[FC9023]

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

 

[FC9023]

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)

 

[RW1]

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

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

 

[Mari]

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!

 

[schoolderman]

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.

 

[hansw]

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