Aug 25 2017
08:39 AM
- last edited on
Jul 25 2018
09:58 AM
by
TechCommunityAP
Aug 25 2017
08:39 AM
- last edited on
Jul 25 2018
09:58 AM
by
TechCommunityAP
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
Aug 25 2017 09:11 AM
Hi Michael,
Just SUBTOTAL will be a right function https://support.office.com/en-us/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939
Aug 28 2017 06:31 PM
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
Aug 28 2017 06:35 PM - edited Aug 28 2017 06:37 PM
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!
Aug 29 2017 03:56 AM
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.