Countif not counting when formula in range

[jokris]

It seems that countif is not counting when I have a formula in my range.
Can I somehow make it count even though cells in same column contains formula?

[JBeaucaire]

The formula in B8 is a circular formula, it is trying to reference cell B8, too, the same cell the formula is in. If you change this formula to some other NON-circular formula, your countif formula will work. Also, the COUNTIF() in B1 needs to be moved out of column B as well.

[jokris]

The formula in B8 is a circular formula, it is trying to reference cell B8, too, the same cell the formula is in. If you change this formula to some other NON-circular formula, your countif formula will work. Also, the COUNTIF() in B1 needs to be moved out of column B as well.

Is there another way for me to count all cells with abc in same column, even when there are formulas below?

[JBeaucaire]

Yes, any formula that is not a CIRCULAR formula (a formula that references it's own cell address in the formula somewhere) will work fine with COUNTIF().

[largecat]

try ?


=IF(COUNTIF(B2:B1000,"abc")=0,"",COUNTIF(B2:B1000,"abc"))

[sanram]

You can use this formula in B1 :

Please Login or Register  to view this content.

[XOR LX]

Please Login or Register  to view this content.

This is an incredibly resource-heavy construction, since, by referencing an entire column within the LOOKUP portion, you are forcing Excel to calculate all 1048576 cells in that column.

What's more, it's not necessary, since COUNTIF(S), SUMIF(S), etc. have a major advantage over most functions in that they suffer no detriment to performance if entire columns are referenced. Hence, you can simply use:

=IF(COUNTIF(B2:B1048576,"abc")=0,"",COUNTIF(B2:B1048576,"abc"))

which will be far, far quicker to calculate than your version.

Regards

[sanram]

This is an incredibly resource-heavy construction

Sorry to say that this is not that heavy. Here

Please Login or Register  to view this content.

is calculating the last used row. So if the last used row in column B is 9 then it will calculate up to 9 only. But with your formula you are forcing to calculate all 1048576 rows. So logically my formula should work faster than you. But at this time I am not able to check the calculation speed as I am little busy.

[XOR LX]

So if the last used row in column B is 9 then it will calculate up to 9 only.

Not true. It will calculate over all 1048576 rows, irrespective of where the last-used row is.

But with your formula you are forcing to calculate all 1048576 rows.

Again, not true. The family of functions COUNTIF(S), SUMIF(S), AVERAGEIF(S), etc. use implicit detection of the last-used cell for the range(s) passed, which means that you can arbitrarily reference ranges as large as you like, with no detriment to performance. Which, as I said, is not at all the case with your construction.

Regards

[Tony Valko]

Sorry to say that this is not that heavy. Here

LOOKUP(2,1/(B:B<>"")

is calculating the last used row. So if the last used row in column B is 9 then it will calculate up to 9 only.

Incorrect.

That formula will evaluate EVERY cell it references, in this case the ENTIRE column.

As a test I entered XX in cell B9.

Then, I timed these 2 formulas:

Formula1: =LOOKUP(2,1/(B:B<>""),B:B)
Formula2: =LOOKUP(2,1/(B1:B25<>""),B1:B25)

I ran 5 times for each and averaged the results:

Formula1: 0.09364 (seconds)
Formula2: 0.00079 (seconds)

So, if the formula only evaluated the used range then both formulas should have (nearly) identical calculation times which they do not.