PDA

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: