Cell Referencing

  • Thread starter Thread starter Karen Howard
  • Start date Start date
K

Karen Howard

Hi,

I have a spreadsheet set up with 2 worksheets. One
worksheet is for the "imported" data and the other is for
updating using cell referencing.

The only problem is that I want the "updated" spreadsheet
to calculate by using the =COUNT function. My problem is
that by the cells being referenced the calculation is
actually adding "blank cells" because of the formula (cell
reference).

How can I get my calculation to only add if there is an
actual "date" in the field and not add "cell referenced
formula's and/or blank cells"? Any suggestions. Thanks!
 
-----Original Message-----
One way:

=SUMPRODUCT(N(ISNUMBER(A1:A100)))

HTH
Jason
Atlanta, GA

.
Hi, Jason!
Click to expand...

Thanks for replying. I tried your formula but it still is
calculating a blank cell because of the "cell referencing"
to another cell in another worksheet. Do you know of
another way. I wish that I could simply set my "options"
so that it "will not calculate blank cells that contain
formulas". Any assistance is greatly appreciated. Thanks!
 
...
Thanks for replying. I tried your formula but it still is
calculating a blank cell because of the "cell referencing"
to another cell in another worksheet. Do you know of
another way. I wish that I could simply set my "options"
so that it "will not calculate blank cells that contain
formulas". Any assistance is greatly appreciated. Thanks!
Click to expand...

If you have problems using COUNT, then you'll have the same problems using
Jason's formula because it's just a longer, slower functional equivalent for
COUNT. Perhaps he's seen something I haven't and could enlighten us about when

=SUMPRODUCT(N(ISNUMBER(A1:A100)))

would ever return a different result than

=COUNT(A1:A100)

Now, if your 'blank' cells that contain formulas evaluate to text such as "",
then they *WON'T* be included in COUNT's result, but they would be included in
COUNTA's result. I realize you've mentioned COUNT, but it's pretty clear you're
either not using COUNT or you're using it incorrectly. Please show us the
*EXACT* formula you're using that isn't evaluating correctly.
 
-----Original Message-----
... Thanks!

If you have problems using COUNT, then you'll have the same problems using
Jason's formula because it's just a longer, slower functional equivalent for
COUNT. Perhaps he's seen something I haven't and could enlighten us about when

=SUMPRODUCT(N(ISNUMBER(A1:A100)))

would ever return a different result than

=COUNT(A1:A100)

Now, if your 'blank' cells that contain formulas evaluate to text such as "",
then they *WON'T* be included in COUNT's result, but they would be included in
COUNTA's result. I realize you've mentioned COUNT, but it's pretty clear you're
either not using COUNT or you're using it incorrectly. Please show us the
*EXACT* formula you're using that isn't evaluating correctly.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
.
No, Jason's formula didn't work either.
Click to expand...

Here's the formula that I have in my worksheet to setup
the cell referencing:

='IMPORTED 3.2.4'!C2


Then in the same column I want it to add the number
of "dates" that have been entered into this column.
Here's the formula for that:

=COUNT(C2:C60)

Now instead of it giving me 12 which is the correct amount
of dates that have been entered, it "incorrectly" gives me
13 because it is also including the "='IMPORTED 3.2.4'!C2"
as a date even though no date appears in that cell.

Please help. Thanks!
 
...
...
Here's the formula that I have in my worksheet to setup
the cell referencing:

='IMPORTED 3.2.4'!C2
Click to expand...

Which cell contains this formula?
Then in the same column I want it to add the number
of "dates" that have been entered into this column.
Here's the formula for that:

=COUNT(C2:C60)

Now instead of it giving me 12 which is the correct amount
of dates that have been entered, it "incorrectly" gives me
13 because it is also including the "='IMPORTED 3.2.4'!C2"
as a date even though no date appears in that cell.
Click to expand...

So what does ='IMPORTED 3.2.4'!C2 evaluate to? Better question, what does the
formula =ISNUMBER('IMPORTED 3.2.4'!C2) return? If it returns TRUE, then COUNT
is correctly including it in its result.

If 'IMPORTED 3.2.4'!C2 is a blank cell, and if ='IMPORTED 3.2.4'!C2 is the
formula in cell C2 of the active worksheet, then cell C2 of the active
worksheet will evaluate to numeric zero, and that would be picked up in COUNT.
If my foregoing assumptions are correct, either replace the formula in C2:C60
with (example for C2)

C2: =IF(ISBLANK('IMPORTED 3.2.4'!C2),"",'IMPORTED 3.2.4'!C2)

or replace the COUNT formula with

=COUNT('IMPORTED 3.2.4'!C2:C60)
 
-----Original Message-----
Karen, try this =IF('IMPORTED 3.2.4'!C2, 'IMPORTED 3.2.4'! C2,"")



.
Harlan,
Click to expand...

Thanks so much for your reply. I tried your formula on my
worksheet and it works perfectly! Thanks to everyone who
has offered their help as well. Enjoy your Weekend and
Happy Turkey Day to Everyone!
 
Back
Top