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
Legum servi sumus ut liberi esse possimus
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?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I thought that the count/sum is required because the sum sums numbers whereas the count counts other stuff. Eh, probably not correct.
Lindie
Legum servi sumus ut liberi esse possimus
And the answers to my questions are ...?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
Legum servi sumus ut liberi esse possimus
=COUNTIF(D48:AK48,"<>0")
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
WHy would you try that when you originally suggestedOriginally Posted by Lindie
=SUMIF(D47:AK47, "<>0")
which is why I asked if you had tried it, the syntax was correct so you changed it? Very odd!
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
Legum servi sumus ut liberi esse possimus
Why not post a sample workbook with your expected results.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Now I think we get it
=SUM(COUNTIF(D47:AK47,{"1","on","H","W","R"}))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
Legum servi sumus ut liberi esse possimus
Posting Permissions
Reply With Quote
