Check for 3 numbers in Cell before totalling

B

Bradley

Hey all,
I'm totalling a row and also checking if E7 has either 315, 316 or 317
in it. Here's what I'm using to check if a cell has 315 and it works:
=IF(E7="315",SUM(I7:O7))
(if E7 doesn't have 315, It won't total, which is what I want)

Is there a way to check for either "315, 316 or 317" in E7?

Many Thanks!

-b
 
R

Rudolf Sommer

Bradley said:
Hey all,
I'm totalling a row and also checking if E7 has either 315, 316 or 317
in it. Here's what I'm using to check if a cell has 315 and it works:
=IF(E7="315",SUM(I7:O7))
(if E7 doesn't have 315, It won't total, which is what I want)

Is there a way to check for either "315, 316 or 317" in E7?

Many Thanks!

-b
Click to expand...

Try =if(OR(E7=315;E7=316;E7=317),SUM(I7:O7),"")

Regards
Ruedi
 
J

JE McGimpsey

One way:

=IF(OR(E7="315",E7="316",E7="317"),SUM(I7:O7),"")

This assumes the the entry in E7 is Text. If it's actually numeric,
remove the quotes.
 
P

Peo Sjoblom

Another way

=IF(OR(E7={315;316;317}),SUM(I7:O7),"")


--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom
 
E

Earl Kiosterud

Bradley,

Note that some of the responses work only with text ("315") in E7, others
only with the number 315. If it could be either, try:

=IF(OR(--E7=315,E7=316,E7=317),SUM(I7:O7),"")

Note no quotes around the numbers.
 
B

Bradley

Bradley said:
Hey all,
I'm totalling a row and also checking if E7 has either 315, 316 or
317 in it. Here's what I'm using to check if a cell has 315 and it
works: =IF(E7="315",SUM(I7:O7))
(if E7 doesn't have 315, It won't total, which is what I want)

Is there a way to check for either "315, 316 or 317" in E7?

Many Thanks!

-b
Click to expand...
Thanks all! Good stuff! Question: In the same equation, I don't want
to total if E7 is blank. Ideas?

Here's the one I'm using:
=IF(OR(E7="314",E7="315",E7="316",E7="317"),SUM(I7:O7),"")

Thanks again,
-b
 
P

Peo Sjoblom

Don't know if you tried the formulas but

=IF(OR(E7={315;316;317}),SUM(I7:O7),"")

will return blank if E7 is blank or if it is not any of the 3

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom
 
B

Bradley

Peo said:
Don't know if you tried the formulas but

=IF(OR(E7={315;316;317}),SUM(I7:O7),"")

will return blank if E7 is blank or if it is not any of the 3
Click to expand...
Peo,
This string works, but still totals if E7 is empty....

-b
 
P

Peo Sjoblom

No it doesn't, but maybe you have your calculations settings set to manual?
You can test that by pressing F9, if it calculates you have it set to manual
under tools>options>calculations,that is also where you can change it to
automatic

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom
 
B

Bradley

Peo said:
No it doesn't, but maybe you have your calculations settings set to manual?
You can test that by pressing F9, if it calculates you have it set to manual
under tools>options>calculations,that is also where you can change it to
automatic
Click to expand...
Thanks, I'll give it a go...

-b
 
Top