Count function problem

[Nevets]

I have a formula in a cell which reads:
"=IF(COUNT(D13:G13)>=2,J13+MAX(H13,$B$43),0)"
When I created this workbook template, the cells from D13 to G13 were blank
until I filled them in, and this formula worked fine. However, I've made a
change to the workbook. Now I've made D13 through G13 equal to the values in
a range of cells from other sheets in the workbook (for e.g. D13 is
"=Sheet1!D13". The above formula is now generating the number from B43 by
default, even though the cells from D13 to G13 are blank.
The "COUNT" help in Excel says:
"Arguments that are numbers, dates, or text representations of numbers are
counted; arguments that are error values or text that cannot be translated
into numbers are ignored."
I assume that I've got a "text representation of a number", so it is being
counted, even though there is no actual number in the cell.
How can I get arount this problem?

 

[Dave Peterson]

You could change your formula.

From:
=sheet1!d13

to:
=if(sheet1!d13="","",sheet1!d13)

 

[Bernard Liengme]

If Sheet1!D13 is blank, then the formula =Sheet1!D13 will return 0. You must
have a format or option setting that prevents zeros from being displayed.
So COUNT(D13:G13) will always return 4
Workarounds:
1) use COUNTIF(D13:G13,"<>"&0) to count how many non-zero values
or 2) in Sheet1 enter a single quote in D13:G13 until you are ready to type
in a number
best wishes

 

[Bernard Liengme]

Other suggestions
=IF(COUNT(Sheet1!D13:G13)>=2,J13+MAX(H13,$B$43),0)"

or

=IF(COUNTBLANK(D13:G13)>2,0,J13+MAX(H13,$B$43))"