Help with conditional subtotals, data pasted in from outside source

Copper Contributor

Hi everyone.

Newbie here looking for help. I am hoping to have a excel sheet that will interpret some data that is copied  into it and then subtotal certain columns. Here's the synopsis:

 

I have a Chrome plug in called table-to-spreadsheet that will (as you might guess) take a table from a website and convert it directly into an excel spreadsheet/csv. It does a great job mapping rows columns appropriately so that is not the issue. 

 

What I am trying to do is take a list of maybe 100 rows. After say 20 rows in there will be a blank row, except the first column will say "SUBTOTAL", and I would like excel to subtotal all of the above 20 rows. Then perhaps there's a second "SUBTOTAL" 30 rows later, which I would like excel to subtotal those 30 rows but NOT the above 20 rows that had already been subtotaled, and so on. Can anyone suggest what functions or, preferably, exactly what formulas to use? I'm thinking "SUBTOTALIF" or other "IF" fuctions might be necessary. 

 

I'll type a crude example here:

 

TOMATO 1.00

TOMATO 1.00

TOMATO 1.00

BANANA 2.00

SUBTOTAL

BANANA 2.00

TOMATO 1.00

ORANGE 3.00

SUBTOTAL

BANANA 2.00

ORANGE 3.00

SUBTOTAL

 

I would just need to subtotal each section, regardless of if the item is a banana, tomato, or orange.

 

Thanks in advance for your help!!!!

Mike

4 Replies

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

Subtotals.JPG

 

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.