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)