MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Mark W or Aladin, could you check my reply to Peggy?


Posted by Eric on September 20, 2001 4:23 AM

The formula I suggested

=SUM(INDIRECT("b"&COUNTIF(B:B,"<>""")-7):INDIRECT("b"&COUNTIF(B:B,"<>''")))

works fine if the data range is left alone, or if new entries are added to the bottom of the list. But if the data is shifted, or an entry is added and then deleted, or even if a row is inserted and then deleted, the formula doesn't adjust.


Posted by Rob Jackson on September 20, 2001 5:10 AM

I was looking at your problem and I think I can see your issue. Your trying to sum a range in column B which you are specifying as 8 entires long. This will only work if the cells B1:B8 are filled. Any less and the formula will crash, any more and the formula will only sum the 'bottom' 8 entries, and then only if the list start in B1 and runs all the way down. If not and it goes really pearshaped. A couple of solutions would be
=SUM(B:B) which will total anything in B. This is the only real way to catch a list anywhere in B. Alternately if your list goes down from B1 all the time then adjust your formula to replace the INDIRECT(Countif()-7) with a simple 1 then it will count the number of entries and set the end of the range accoringly. Sorry if I'm missing the point...

R.

Posted by Eric on September 20, 2001 6:55 AM

Thanks Rob, to add to your point...

First, thanks v. much for taking a look at this-

Part of what Peggy was asking for was for the bottom 8 cells in the column to be summed, considering that there would be additional entries to the column over time.

So you're absolutely right, if there are less than eight entries, the formula bombs, but if not it appears to work appropriately. But at this point fixing the "less than eight" bomb would be like rearranging deck chairs on the Titanic!

What's puzzling me is the following:
if I take the model data and add another entry to the bottom, the formula adjusts correctly. If I then go back and delete that bottom entry, the formula does NOT recover, and instead reports the value from the previous calculation. The same thing happens if I delete the row of the previous entry.

Posted by Rob Jackson on September 20, 2001 7:24 AM

Re: Thanks Rob, to add to your point...

I tried it on my system, works fine. No probs with recalculation at all.

Have you checked Tools/Options/Calculation to make sure thats set correctly?

I am running 2000, are you. Is it possibly a glitch in a previous verson.

Silly thought but I don't suppose the totals of the last eaght happen to be the same before and after the change?

Posted by Juan Pablo on September 20, 2001 7:31 AM

Re: Thanks Rob, to add to your point...

I'm using '97 and i got the same thing... i replaced the COUNTIF with this.

SUMPRODUCT(NOT(ISBLANK(A1:A65535))) and worked fine... but i can't put A:A because it would return 1 (Even if there are 10, 100 or 1000 rows of data)... also if i put the sum in C1, i put the above formula in D1 because if i just write one single formula the calculation is very slow.

Juan Pablo

---------------------

Posted by Eric on September 20, 2001 8:02 AM

Im a little slow today (all days)- does your sumproduct formula sum only the last 8 cells w/ data? (NT)

Posted by Juan Pablo on September 20, 2001 8:14 AM

Re: Im a little slow today (all days)- does your sumproduct formula sum only the last 8 cells w/ data? (NT)

No, what it does is replace your
COUNTIF formula that doesn't seem to update when a row is deleted... it does the exact same thing

SUMPRODUCT(NOT(ISBLANK(B1:B65535))*1) instead of

COUNTIF(B:B,"<>""")

Try it...

Juan Pablo

Posted by Eric on September 20, 2001 8:16 AM

The countif function seems to be the source of my woes

Thanks again Rob, all good suggestions but none panned out- I've actually replicated this problem on a WinME machine (this one's NT).

Don't mean to mire the posts with this- but could you try this and tell me what happens?
in c2 type
=countif(B:B,"<>0") or even
=countif(B1:B65536,"<>")
I get a count of 2! It keeps reporting 2 until I have at least 3 cells with data in col B. Then, once it's passed the 3 count, if I remove one of the values, the countif doesnt catch it!

Posted by Eric on September 20, 2001 8:21 AM

Jeez Louise- I knew I was being stupid about that- thx!

Now that's behaving in the way I'd intended! Thanks!