Number of cells with info in

  • Thread starter Thread starter Aladin Akyurek
  • Start date Start date
I have a list in column A, ranging from A1:A30.

Now, I want to work out the total of cells within that range which contain
information, regardless of what that information is. So, for example, if
only two cells within this column were filled in, I want to work this out
and have this as an answer, ie 2.

Any ideas?

Thanks
 
Ok, here is what I have got so far for this, however I have been told that
the formula is too long. TBH, I have to agree. Can anybody shorten this for
me please?

=ROWS('[March.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[March.xls]Performance
Indicators'!$B$22:$B$33)+ ROWS('[April.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[April.xls]Performance
Indicators'!$B$22:$B$33)+ ROWS('[May.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[May.xls]Performance
Indicators'!$B$22:$B$33)+ ROWS('[June.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[July.xls]Performance
Indicators'!$B$22:$B$33)+ ROWS('[August.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[August.xls]Performance
Indicators'!$B$22:$B$33)+ ROWS('[September.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[September.xls]Performance
Indicators'!$B$22:$B$33)+ ROWS('[October.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[October.xls]Performance
Indicators'!$B$22:$B$33)+ ROWS('[November.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[November.xls]Performance
Indicators'!$B$22:$B$33)+ ROWS('[December.xls]Performance
Indicators'!$B$22:$B$33)-COUNTBLANK('[December.xls]Performance
Indicators'!$B$22:$B$33)
 
Ok, here is what I have got so far for this, however I have been told that
the formula is too long. TBH, I have to agree. Can anybody shorten this for
me please?
Click to expand...
...

How many almost identical ROWS calls do you have? When could any of them ever
return a value differing from what the others return?

Does x-a+x-b+x-c = x+x+x-a-b-c?

Does x+x+x = 3*x?

As for the COUNTBLANK calls, if any of them other workbooks are ever closed,
your COUNTBLANK calls referring to the closed workbooks will return #VALUE!
errors, so your formula as a whole will return an error value. If you're dealing
with other workbooks, the safest way to count cells evaluating to anything other
than "" is

=SUMPRODUCT(--(Reference<>""))

This will propagate the first error value in Reference, if any. To include cells
evaluating as errors in the count, use

=SUMPRODUCT(--ISERROR(1/(Reference="")))

Whoever told you the formula was too long needs to be told in turn that some
formulas need to be long.
 
Thanks all!

Seems to have worked so far!

Harlan Grove said:
...
..

How many almost identical ROWS calls do you have? When could any of them ever
return a value differing from what the others return?

Does x-a+x-b+x-c = x+x+x-a-b-c?

Does x+x+x = 3*x?

As for the COUNTBLANK calls, if any of them other workbooks are ever closed,
your COUNTBLANK calls referring to the closed workbooks will return #VALUE!
errors, so your formula as a whole will return an error value. If you're dealing
with other workbooks, the safest way to count cells evaluating to anything other
than "" is

=SUMPRODUCT(--(Reference<>""))

This will propagate the first error value in Reference, if any. To include cells
evaluating as errors in the count, use

=SUMPRODUCT(--ISERROR(1/(Reference="")))

Whoever told you the formula was too long needs to be told in turn that some
formulas need to be long.
Click to expand...
 
Back
Top