g-lassie recently had a problem to solve with her spreadsheet. Here's how the sheet is set up:

Column A is a series of product names. They are grouped in certain categories, but the number of products in each group is not uniform (some groups have 3 products, some have 10, etc). Column B has a count of each product. After each group, Column A will say "Total" and Column B will have the total # of products in the group. The goal is to have in a new column the % of total: count of the particular item in the group / total count of items in the group.

I solved this for her by having column C =VLOOKUP("Total", $A2:$A$1000

******, 2, FALSE) and column D = IF(A2="Total","",B2/C2), but I know this isn't ideal and I can't think of how to word a google search to find a solution. I'm sure I'm missing something pretty easy here.

Any way to do this in one nice formula? Please no array formulas if possible, I'd rather not open up that can of worms to her.

******The total number of rows in the sheet

Example with desired output:

Code:

PRODUCT COUNT
x1 52 5%
x2 301 29%
x3 293 29%
x4 219 21%
x5 157 15%
Total 1,022
y1 19 3%
y2 183 30%
y3 163 26%
y4 141 23%
y5 114 18%
Total 620