Check for 3 numbers in Cell before totalling

  • Thread starter Thread starter Bradley
  • Start date Start date
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
 
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
 
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.
 
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
 
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.
 
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
 
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
 
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
 
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
 
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
 
Back
Top