Forum Discussion
Help with conditional subtotals, data pasted in from outside source
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
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!
