Formula will not work

[Guest]

I have a workbook set up which has year-to-date running totals.
The report is pulling the previous months numbers from different sheets that
I have added to the end of the workbook once the month is complete. The
formula I have been using,

=SUMIF('Int Mar 07'!A:A,"four winds",'Int Mar 07'!B:B)

has worked fine until this month. When I change the formula to reflect April
(the latest month), all I get are "0"'s. When I tested the formula to pull
from different sheets, still within the workbook, it computes fine. Could
there be something corrupt with the April sheet I just added?

I'm relatively new to Excel so any information would be greatly appreciated!

Thanks!

Indymanny

 

[Don Guillett]

copy/paste your formula here

 

[Dave Peterson]

Maybe "four winds" doesn't match any cell in April's column A?

Maybe the values in column B of that worksheet aren't numbers--maybe they just
look like numbers (text masquerading as numbers).

If you put these in a couple of empty cells in the April worksheet
=count(B:B)
=counta(B:B)
do they match?

The first counts numbers. The second counts non-empty cells.

 

[Guest]

Thanks, Dave for the reply. Unfortunately, nothing is working as of yet. I've
double checked the names are correct ("four winds", etc.), made sure all of
column B on the April document are Numbers, and tried your "count" formula -
which when I did the first one, it gave me an error that it was trying to do
a "circular reference", and the second one (counta) just gave me a "0".
Should I be checking something else on the April sheet?

 

[Dave Peterson]

Put the formulas on the April worksheet, but don't use a cell in column B.

If =count(B:B) returns 0, then you don't have any numbers in column B.

If =counta(B:B) returns 0, then you don't have anything in that column.

 

[Guest]

While I was at lunch, a co-worker took a shot at the problem. All she did was
re-type all the numbers in the B column on the April worksheet and then it
worked. Is there a reason for this, so I know what to watch out for the next
time?

Again, thanks for all your help!

 

[Gord Dibben]

Somewhere in the copy process the numbers were changed to text format.

In future rather than re-typing all the numbers, just format all to General then
copy an empty cell.

Select the range of "numbers"

Edit>Paste Special(in place)>Add>OK>Esc

This coerces Excel to see them as real numbers.


Gord Dibben MS Excel MVP

While I was at lunch, a co-worker took a shot at the problem. All she did was
re-type all the numbers in the B column on the April worksheet and then it
worked. Is there a reason for this, so I know what to watch out for the next
time?

Again, thanks for all your help!