CountBlank for "Adaptive" Range

I am trying to count a range for blank cells using CountBlank(), however, the range I need to use is, for example, "A:A".
When I specify an entire column, however, I get a massive number of blank cells.

Is there a way to tell CountBlank to only return the number of blanks that have data in adjacent columns?

This is an issue because sometimes I need to count A1:A234 but another day my data my span from A1:A500 for example.

Thanks!

Hi, try this formula:

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi Alvin,

For some reason that isn't working for me.
Also, I was hoping to not have to specify another range. Otherwise, I can just use:

Please Login or Register  to view this content.

Thanks,

You'll need to specify another range (i.e. B:B) because your criteria is to verify whether there's any data in adjacent column.
Sure, you can use COUNTIFS too but probably SUMPRODUCT would be slightly faster (I guess)



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Makes sense. I was just wondering if there was a way to have Excel intelligently only calculate cells that are part of your dataset and not the entire possibly number of cells in a column.

Thanks,

Thanks Tony, good to know I was approaching it right. Still wish there was a way for Excel to intelligently ignore data outside of your dataset but I guess that is asking too much (there are probably few reasons why someone would want to include all blank cells up to Excel's limit in their formulas lol)

I already have my answer. Excel does not treat rows as records within a dataset.
Within Access, for example, if you ask to return the number of records within a column that are not blank, Access is smart enough to know that you don't mean "all of the cells that COULD be blank".
If I tell Excel to count blanks for A:A, and my dataset only spans from A1:B12, Excel will return blanks for the maximum blank cells that a column contains (65,000+)

Make sense?

Originally Posted by Tony Valko

COUNTIFS will only calculate up to the used range even if it's referencing the entire columns.
SUMPRODUCT will calculate EVERY cell it references.

Thanks for highlighting, you're right and the explanation were very useful!
Plus, I just did an experiment by disabling multi-threaded calculation and get 2'10 for SUMPRODUCT and 2'03 for COUNTIFS



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Not sure if this helps, you can try name your data as a table and use the name in your formula.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

When I use countif, it doesn't only calculate within the used range . . .

Tony, my countif statement returned 65536
I am using Excel 2010. Perhaps there is a setting to control this functionality? That is what brought me here to the forum was that Excel is not including just the "used range". Previously, Excel was doing so (on my old computer from another job)

Here's a screenshot:

countif.png

Hmm, good catch. It turns out my Excel was stuck on "Compatibility Mode".
I've since turned it off but my countif function still calculates the maximum number of blanks (now 1,048,576).
I followed the same process . . .

There must be some sort of setting that controls this. If we're both using Excel 2010 then they should work in the same way.
Thanks for your help, I will track down the setting separately.