Discussion Locked

Excell Linking

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Heres my problem: I have two worksheets-one is the complete master worksheet which has all the salespeople data and the other worksheet is the individual salesperson worksheet which is linked to the master worksheet. On the linked worksheet, I havethe formula: =count(b3..m3) and it returns a value of 12. Well, there is 12 colums but only 3 has the actual results from the master sheet, the other 9 are blank but have the linking reference in them. How can I get it to only count the cells thatcontain the data that has been returned?

5 replies

by pvp

You may want to use COUNTA or COUNTIF instead of the vanilla COUNT.

by peyison

You could try: =COUNTIF(B3:M3,">0")

This counts cells only if their value is greater than zero.

On the 9 that are blank, they probably have a value "0" and you have the option to hide zero values turned on. (Tools - Options - Under Window options > Zero values) If you enter the formula =A1 in a cell, and A1 is blank, the formula returns a 0.

The case where this won't work is if the formula really should be returning a zero - so you would want to count it.

Hope this helps.

This discussion is closed to new replies.