Criteria Syntax in SUMIF formula

[RollieG]

I originally asked:

How do you use SUMIF, when your criteria is one of two(or multiple) values?

For Example, I want to use:

=SUMIF(A2:A10, ($C$1 or $D$1), B2:B10), but I know ($C$1 or $D$1) is not the
correct syntax.

To which I got this answer:

Just add them:

=SUMIF() + SUMIF()
--
Gary''s Student - gsnu200810

This will not solve my dilemna. I replied to that with this, but i'm afraid
the post will not be read. So I am re-asking the question.


Thank you, but this is what I want to do:

Here is the actual formula that I need to change:

=IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS$2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000)).

I want to use the Find and Replace function to replace the criteria "$C$2"
with the desired expression ($C$2 or $K$1).

I didn't originally design this worksheet, and I am stuck with the original
logic. There are actually over 150 tabs with 31 formulas on each sheet to
change. I wanted to use the Repace all function, selecting multiple sheets
(tabs), so I can change all the formulas at one time.

Changing it with your suggestion would mean, if I am understanding you
correctly, would change the formula to :

=IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUMIF('29'!$AS$2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000+SUMIF('29'!$AS$2:$AS$5000,$K$1,'29'!$AT$2:$AT$5000))

The Sheet tab '29" refers to a sheet with data from the 29th of the month.
The next formula on the next row is:

=IF(ISBLANK('30"AS$2:$AS$5000),"",SUMIF('30'S$2:$AS$5000,$C$2,'29'!$AT$2:$AT$5000)). So, I cannot fill down formulas.

This is why I would like to change the formula using the Find and Replace
method.

I thought there could be an expression in the criteria part of the formula
that can refer to one of two or more values.

 

[John C]

As I stated in your other post that people still read, you cannot do what you
are wanting to do. There is a 'way' that SUMIF can take multiple criteria,
but it still won't work for your purposes.
Check Dave's response to this post
http://www.microsoft.com/office/com...d381&mid=bf229caf-3ba3-43bd-9a77-197b6dc1d381
It shows you can have 2 values within the sumif, but it changes the formula
to have an additional SUM around your SUMIF formula.

Why are you adverse to changing the formula if it not ONLY achieves the
desired result but is also easier to modify in the future?

 

[RollieG]

Because it will take a lot longer to change the formulas. Using a simple find
and replace method would not take as long, and I would not have to try to
enter this formula as an array formula. Dave Peterson's suggestion is what I
would want to do, but it only seems to work when I enter the actual strings,
instead of the cells.

 

[John C]

It also requires a modification of your formula, as you need an additional
SUM around your SUMIF formula. Yes, as RagDyeR indicated in your other post,
these are literal strings. You could use INDIRECT in conjunction with it,
however, you still run into the problem of having to modify other parts of
your formula because you need to add the SUM around the SUMIF.

 

[Gary''s Student]

You can use a single SUMIF formula if you are willing to use a helper column.

Say A2 thru B10 contains:

1 2
1 3
1 4
2 5
2 6
2 7
3 8
3 9
3 10


With C1 containing 1 and D1 containing 2

In E2, we enter:
=IF(OR(A2=$C$1,A2=$D$1),1,0) and copy down.

The values in column E reflect BOTH criteria. This allows a single sumif
formula:

=SUMIF(E2:E10,1,B2:B10)