Criteria Syntax in SUMIF formula

[RollieG]

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.

 

[Gary''s Student]

Just add them:

=SUMIF() + SUMIF()

 

[John C]

As a followup, since we don't know how the values in $C$1 and $D$1 are
generated, is there ever a chance they will be equal to each other? If so,
you could modify Gary's suggestion like so:
=(SUMIF(...)+SUMIF(...))/(1+($C$1=$D$1))

 

[RollieG]

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

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 refers to one of two or more values.

 

[John C]

You could always change up the formula a little bit. Assuming you have the
day of the month in a column, you could always combine the formula with
indirects
=IF(ISBLANK(INDIRECT("'"&A10&"'!$AS$2:$AS$5000")),"",SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$C$2,INDIRECT("'"&A10&"'!$AT$2:$AT$5000"))+SUMIF(INDIRECT("'"&A10&"'!$AS$2:$AS$5000"),$K$1,INDIRECT("'"&A10&"'!$AT$2:$AT$5000")))
This would be the formula for row 10 (assuming you have header information
above like in C2 and K1. This also assumes your day numbering is in column A.
Modify as needed. You could also use different methods to generate the tab
name, for example, instead of A10 for the 1 tab, you could use ROW()-9 in
place, and that will evaluate to 1.

 

[John C]

NOTE: This is an **array** formula, and therefore must be entered using
CTRL+Shift+Enter

 

[RollieG]

Thank you, but I just wanted to know one thing:

Can the criteria in a SUMIF refer to one of two (or more) values?

 

[John C]

It can, sort of, but not in the way you want. Your overall formula will have
to be modified.

 

[RagDyeR]

Try something like this:

=SUM(SUMIF(A2:A10,T(INDIRECT({"C1","D1"})),B2:B10))

This assumes that the values in C1 and D1 are Text.
If they're numeric, replace the "T" with an "N" (no quotes).

Applying this concept to your formula, see if this works for you:

IF(ISBLANK('29'!$AS$2:$AS$5000),"",SUM(SUMIF('29'!$AS$2:$AS$5000,T(INDIRECT({"C2","K1"})),'29'!$AT$2:$AT$5000)))

Don't forget about the "T" or the "N" to define the contents of C2 or K1.

Also, since they're within the Indirect() function, you don't need the
absolute $'s for C2 and K1.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Thank you, but I just wanted to know one thing:

Can the criteria in a SUMIF refer to one of two (or more) values?

 

[RagDyeR]

To answer your basic question about multiple criteria for the Sumif()
function, the answer is *yes*.

You wrap the Sumif() in the Sum() function, and you use an array constant
for the criteria:

=Sum(Sumif(A1:A10,{25,50,75},B1:B10))
=Sum(Sumif(A1:A10,{"Tom","Dick","Harry"},B1:B10))

However, array constants will *not* accept cell references.

You therefore must include the Indirect() function to use cell references.

As in my earlier example:

=SUM(SUMIF(A2:A10,T(INDIRECT({"C1","D1"})),B2:B10))

Using either a "T" or "N" to denote the contents of the cells being used as
the criteria.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Thank you, but I just wanted to know one thing:

Can the criteria in a SUMIF refer to one of two (or more) values?

 

[John C]

The issue the OP wants to do is to be a FIND/REPLACE for his formulas. Which
because the SUMIF formula must be wrapped in a SUM formula, will not work.