View Full Version : Countif: how to use values and not formulas?
jungix
08-02-2006, 06:51 AM
=COUNTIF(AG7:AG1000,"<>""")
I used this formula in Excelm and it gives me 994, whereas not every cell contains data. But everycell contains a formula, such as
=IF($A7=AG$6,$J7,"") in AG7
Then since the formulas are <>"", it counts them. Is it possible to specify the Value in the Countif or have I got to write a macro for this simple thing?
Zack Barresse
08-02-2006, 07:29 AM
Hi there jungix!
You can use ..
=SUMPRODUCT(--(LEN(AG7:AG1000)<>0))
HTH
Bob Phillips
08-02-2006, 07:30 AM
How about
=SUMPRODUCT(--(LEN(AG8:AG1000)>0))
Zack Barresse
08-02-2006, 07:33 AM
Hmm, maybe I have slippy fingers this morning.. ;)
jungix
08-02-2006, 07:46 AM
Thanks, it seems to work.
If it is not asking too much could you just explain me how you find this formula?
I don't understand the "--" and neither do I understand what LEN(AG8:AG1000) means
jungix
08-02-2006, 07:46 AM
Thanks, it seems to work.
If it is not asking too much could you just explain me how you find this formula?
I don't understand the "--" and neither do I understand what LEN(AG8:AG1000) means. I just tried it alone and it gives me an error
Bob Phillips
08-02-2006, 08:35 AM
Thanks, it seems to work.
If it is not asking too much could you just explain me how you find this formula?
I don't understand the "--" and neither do I understand what LEN(AG8:AG1000) means. I just tried it alone and it gives me an error
-- - See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.
LEN(AG8:AG1000) just checks whether any cell has any output, it then uses the -- to transform it to 1/0 which SP adds up.
jungix
08-02-2006, 10:32 AM
Ok thank you very much.
I now understand what this -- means. As for the LEN operator, I knew it for strings, but I did not know you could use it for a range and that it would give you an array with the true/false for each cell.
Your way is so simple and so nice.
I'm now less stupid than I was this morning :beerchug:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.