Forum Discussion
Help with conditional subtotals, data pasted in from outside source
Hi Michael,
Just SUBTOTAL will be a right function https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939
Hello Sergei,
I appreciate the help but I'm looking for an automated way for excel to identify what to subtotal. Here's the workflow I was hoping for:
1. I copy a worksheet from my corporate website.
2. I paste this content into excel, resulting in properly populated rows and columns.
3. Either upon pasting or via a macro, Excel identifies the word "SUBTOTAL" (actual text, not the function "=SUBTOTAL". If that's not clear I could also have it say "NOTE" or "SUBTITLE, etc.) in the first column, Column A.
4. Once identified, in a separate column (say 5 over, so column F), Excel subtotals all above values.
5. Concurrently, excel identifies the other "SUBTOTAL" designators in Column A and subtotals each of those in Column F, while not inlcuding values above from the previous subtotal.
I know I can manually subtotal each of these. I was looking for a macro or function to do this automatically as I may have as many as 10 separate sections of the worksheet that need to be subtotaled individually.
Thank you again! Sorry if this wasn't extremely clear.
Mike
I'll try to prepare a better example as well:
COKE 1L $3.00
COKE .5L $2.00
PEPSI 1L $4.00
SUBTOTAL xxxxx<---subtotal automatically applied here, so $7.00
PEPSI .5L $2.50
SPRITE 1L $5.00
COKE 1L $3.00
7UP 1L $6.00
SUBTOTAL xxxxxxx<---subtotal again automatically calculated here, so $16.50, but NOT including above $7.00
PEPSI 1L $4.00
.....
and so on.
Thanks!
Hi Michael,
Okay, i see.
Perhaps that could be done with macro, but i don't work with them and can't help here. Quick search gives VBA for similar case, you may try it https://stackoverflow.com/questions/30573033/sum-column-between-zeros
Another option if in your source data you have one more column which identifies groups for which you'd like to subtotal. When you may use Subtotal from Data section in ribbon to add sums on each change of such group name, result will be like
That could be also helper column with running total which starts from each row where the cell with price is zero.
Perhaps something else, not sure now.
