Sum Between Blanks

**sleazyfish** · 10-16-2009, 06:56 AM

Hi,
I have a worksheet(B) that is populated by another worksheet(A). This data is changing on a daily basis. Groups of data vary in number and are separated by blank cells.

I want to subtotal between blank cells automatically without using XL's built in subtotal. I do achieve the right results using the subtotal facility however it means i have to reset all the formulas then run the subtotal to update from the previous day.

This would be ok for me but i have built this system for a novice excel user. I am therefore looking to make this as easy as possible.

example:

---A ---B
1 bud 6
2 bob 5
3 fred 4
4 TOTAL 15
5 ted 2
6 zed 5
7 ped 1
8 TOTAL 8

In the above example rows 4 and 8 are currently blank. Also tomorrow there might be 10 entries before a blank.

Thanks in Advance

**zbor** · 10-16-2009, 07:17 AM

Probably not best soltuio but I've came with this....

Book1.xls

**jj72uk** · 10-16-2009, 07:27 AM

Taking it a step further... if you dont like the #NAME

Use:

=IF(ISERROR(RANDBETWEEN(10,100)),"",RANDBETWEEN(10,100))

**Palmetto** · 10-16-2009, 07:27 AM

Perhaps you could use a Pivot Table based on a dynamic named range, with the named range padded to accomodate extra rows. It would then only be a matter of refreshing the Pivot Table.

**DonkeyOte** · 10-16-2009, 07:28 AM

I'm afraid I don't understand your requirements... you say the data is changing every day so you can't use Subtotal feature ... can you elaborate on the workflow. If the data is being overwritten then insertion of formulae isn't going to work either (ie you're looking at VBA).

**sleazyfish** · 10-16-2009, 07:48 AM

Thanks for all of your rapid responses.

zbor, this solution was excellent!. I included a slight modification to eliminate duplicates:

=IF(AND(C1="",C2=""),"",IF(ISBLANK(C2),SUMIF(D:D,D2,C:C),""))

I can incorporate the above example into my worksheet. Although I would have preferred the total to be in the same column as the quantity.

Is this possible without VBA as outlined by DonkeyOte?

**DonkeyOte** · 10-16-2009, 07:57 AM

I would have preferred the total to be in the same column as the quantity.

Is this possible without VBA as outlined by DonkeyOte?

No (or at least not that I can think of). You would need to add/remove the Subtotal feature and/or use Special Cells methods - either way avoiding VBA would require manual intervention.

Just to illustrate (manual) special cells method...

First highlight column B then

1 - press F5
2 - select Special
3 - select Blanks
4 - press OK
5 - hold ALT and type =
6 - hold CTRL and hit ENTER