if statement for summing cells

[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

[[email protected]]

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

[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?


"[email protected]" wrote:

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

[[email protected]]

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

[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


"[email protected]" wrote:

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

[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

[Gavin0268]

Steve
That looks perfect, just what I was after
Many thanks
Gavin

"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=538382
>
>

[SteveG]

Gavin,
Glad I could help.

Regards,

Steve