PDA

View Full Version : Solved: simplifying formulae



Lindie
07-24-2008, 02:48 PM
I have this formula
=SUMIF(D47:AK47,1) + COUNTIF(D47:AK47, "on") + COUNTIF(D47:AK47, "H") + COUNTIF(D47:AK47, "W") + COUNTIF(D47:AK47, "R")

There is only one value- namely zero -0- that should not be counted. Couldn't the above formula be simpler by something like

=SUMIF(D47:AK47, "<>0")

I have tried several variations on this theme this but just don't get anywhere.

thanks
Lindie

Bob Phillips
07-24-2008, 04:28 PM
Well, apart from the fact that your original formula adds a count to a sum, two different beasts, I can only ask, did you try it? If not, why not. If you did, what was wrong with it?

Lindie
07-25-2008, 02:39 PM
I thought that the count/sum is required because the sum sums numbers whereas the count counts other stuff. Eh, probably not correct.
Lindie

Bob Phillips
07-25-2008, 04:09 PM
And the answers to my questions are ...?

Lindie
07-26-2008, 01:38 PM
Well, I tried =sum(a12:a44, <>0) and that is wrong so I'm rather stuck. Tried sumif and countif too; I'm rather stuck.
L

mdmackillop
07-26-2008, 03:09 PM
=COUNTIF(D48:AK48,"<>0")

Bob Phillips
07-26-2008, 05:04 PM
Well, I tried =sum(a12:a44, <>0) and that is wrong so I'm rather stuck. Tried sumif and countif too; I'm rather stuck.
L

WHy would you try that when you originally suggested

=SUMIF(D47:AK47, "<>0")

which is why I asked if you had tried it, the syntax was correct so you changed it? Very odd!

Lindie
07-30-2008, 01:08 PM
Sorry I have not been clearer. =SUMIF(D47:AK47, "<>0") works fine if I only use numbers. I, however, also have text values like "on", "h" hence I use the long formula
=SUMIF(D47:AK47,1) + COUNTIF(D47:AK47, "on") + COUNTIF(D47:AK47, "H") + COUNTIF(D47:AK47, "W") + COUNTIF(D47:AK47, "R")

This takes care of both text and numeric values. It works fine but I wondered whether there is a way of shortening this.
Perhaps I am too obsessed by keeping things simple.

mdmackillop
07-30-2008, 01:11 PM
Why not post a sample workbook with your expected results.

Bob Phillips
07-30-2008, 02:38 PM
Now I think we get it

=SUM(COUNTIF(D47:AK47,{"1","on","H","W","R"}))

Lindie
07-30-2008, 03:16 PM
That's it; so simple when you know how! The {} these are not array {} are they? Why don't () work here?
A mutlitude of thanks
Lindie