Bizarre/frustrating excel problem..

Okay, so this is something I'm working on for work.

- The tab "60 to 90" contains data about accounts that are between 60 and 90 days overdue. As you can see, same thing with "90 to 120" and so on. In each of these tabs, Column G is either a "1" or "0" representing whether we can collect on these accounts. Column C is their current balance.

Now in Cell F3 on this summary page that you're looking at, I have the following formula:

[code:1]=SUM(IF('60 to 90'!$G$2:$G$3000=1,'60 to 90'!$C$2:$C$3000,0))[/code:1]

Which looks in tab "60 to 90", counts all the ones that have a collectable value of "1", and sums the balances of those that do. This works fine.

In Cell F4, I have the SAME formula, but with "60 to 90" changed to "90 to 120" and it comes out as 0. I've checked on the "90 to 120" tab itself, and there is plenty of data, with high balances and "1" values in column G.

To investigate further, I copied the formula from F4 and pasted the EXACT same formula into G3, which you can see. But now, it comes out with the correct value.

Further, I've drag-filled from G3 down to G22, and look at the results! Because these are all absolute references, there is ZERO change to the formula, but sometimes it's "0" and sometimes it's "3714860" ?

Also, I've checked the cell formatting of both this tab and the data tabs and there's no issue there, certainly nothing that accounts for why the line above works but this one doesn't....

Any ideas? Driving me nuts!

havent got time to look at the details but wouldnt it be better to keep all the info on one page and have a age column?

Then you can query for all in a range and it simplifies the lookups.

ie across the columns you will have:

ID
Active (yes/no)
Name
Date
Amount
Reason
Status
Age (calculation).
etc

I know its not your question but it might make things simpler down the line.

perhaps you should be using count? try using a pivot table.