Thread: Counting cells and ignoring references

Hello,

I have a range of cells (B6:M6) and I want to count how many have data. My problem is that all of the cells reference cells on another worksheet. Every cell starts out like =Bethlehem!B6. I want to ignore cells that have a reference in it when I count. I know how to do it if you want to ignore these cells as follows: =countif(b6:m6,"<> 0"). Those cell references return a 0 if they have no data in them. However, a user might actually enter a 0 in the referenced cell and I need to count that one. The above formula would exclude it. Any suggestions?

Thank you very much,

Dave

Hi Dave,

Can you adjust your formulas to something like:

=IF(Bethlehem!B6="","",Bethlehem!B6)

if so, you could then just use

=COUNT(B6:M6)

to get the number of cells with values in them.

Dave, I think Paul was suggesting you alter your initial formulae in B6:M6 such that should the target cell be blank a Null is returned to the cell rather than 0, so

B6: =IF(Bethlehem!B6="","",Bethlehem!B6)
copied to M6

The above simply checks to see if the cell B6 on Bethlehem sheet is blank or not, if it is blank it returns a Null value else it returns the content of B6 (this will include valid 0 entry)

Once the above is input you can then as advised by Paul use the COUNT function if we're assuming B6:M6 on Bethlehem sheet contain numerics

=COUNT(B6:M6)

The Nulls you returned for the otherwise blank cells will be ignored in the above as a Null is in essence a 0 length text string and thus not numeric, COUNT only looks at numeric values.

If you're returning a mixture of text and numbers then another alternative would be:

=COUNTA(B6:M6)-COUNTBLANK(B6:M6)

This works because COUNTBLANK treats Null as Blank (COUNTA does not).