if statement for summing cells

G

Gavin0268

Hi there
I can do this in a clunky way (I think) but I'm looking for a more sensible
method. I have an output spreadsheet from an instrument, and need to tinker
with the numbers to display different things depending on the value of the
cell.

If the value is -ve, I display 'ND'; if the value is <0.2, I display TR.
Otherwise then umber is displayed. So far so good.

I then need to sum a number of these cells (say 4 of them). If there are any
numbers (ie not all of the cells are ND or TR), I sum the numbers, ignoring
ND or TR; if all the cells are ND I display ND, if all the cells are TR, or a
mixture of ND or TR, I display TR. This is where I get stuck! Any ideas?

Thanks in advance
 
I

ivan.raiminius

Hi Gavin,

I assume that by displaying "TR" you mean assigning value "TR" using if
or any other condition.

You can incorporate countif into your formula. I mean
countif(a1:a4,"tr") will give you number > 0 if any "tr" is found in
the range.

Regards,
Ivan
 
G

Gavin0268

Hi
The individual cell IF statement is (e.g.)
=IF(V48<0,"ND",IF(V48<0.2,"TR",V48)), where V48 is the original value.
If we assume I'm then summing V46 to V49, say, I need to check if there are
any numbers, and sum them (sum=sum of numbers, ignoring ND & TR); or all ND
(sum=ND); or all TR or mix ND/TR (sum=TR).
Do you think I need nested countif's for ND& TR's?
 
I

ivan.raiminius

Hi,

you don't need to check for numbers, sum ignores non-numerics.

Supposing, that formula =IF(V48<0,"ND",IF(V48<0.2,"TR",V48)) is in W48
(better to tell in range W46:W49, and you need the total in W50 and
following:
W50 = sum(W46:W49) - sum of numbers
W51 = sumif(W46:W49,"ND",v46:v49) - sum of ND
W52 = sumif(W46:W49,"TR",v46:v49) - sum of TR

Does this help? Maybe I didn't understand your question fully (my
understanding changed after your second post).

Regards,
Ivan
 
G

Gavin0268

Hi Ivan
We're almost there, but maybe I'm not explaining very well! Here's some
examples
0.5,0.5,0.5,0.5: Sum=2
0.5, 0.5, TR, ND: Sum = 1.0
TR,TR,TR,TR: Sum = TR
TR,ND,ND,TR: Sum = TR
ND,ND,ND,ND: Sum = ND
with obviously numerous variations.
Thanks for the quick response
 
S

SteveG

Gavin,

Try something like this.

=IF(SUM(F6:I6)>0,SUM(F6:I6),IF(AND(COUNTIF(F6:I6,"TR")>0,COUNTIF(F6:I6,"ND")>0),"TR",IF(AND(COUNTIF(F6:I6,"TR")>0,COUNTIF(F6:I6,"ND")=0),"TR","ND")))

I used F6:I6 for my range to evaluate, change those as needed. In your
sample data this returned 2,1,TR,TR and ND respectively. This assumes
that if there are no numbers, there will always be "TR"'s and/or "ND"'s
in the cells.

Does that help?

Steve
 
Top