• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find total sum of group in table to extract percent of group total per row

We are looking to create a formula that will find the total of each group in a table in order to extract a percent completed per row. Grouping consist of 4 or 5 columns of data. Please refer to attached excel file. File includes a small amount of sample data - table may have up to 30- 50k rows of data. Once we have the percent per row we will create a pivot table that will summarize the data with slicers based on various columns.

Thank you in advance for your expertise and helpfulness.

Please find attached excel sample file
 

Attachments

  • Percent of Total.xlsx
    14.5 KB · Views: 8
Thank you for the prompt response ...
I may not understand correctly or asked the question properly ... but, I think this formula assumes we already have a sub-total for column I (Total MSQ per group) - which we do not. We would like the formula to calculate the sub-totals of the groupings (based on similar field attributes in columns A,B,C and D then divide by actual on hand to give us a percent by row. Correct me if I'm wrong.

Thank you
 
Thank you for the prompt response ...
I may not understand correctly or asked the question properly ... but, I think this formula assumes we already have a sub-total for column I (Total MSQ per group) - which we do not.

Ok, use this formulae:
=H2/SUMIFS(G$2:G$52,A$2:A$52,A2,B$2:B$52,B2,C$2:C$52,C2,D$2:D$52,D2)

Blessings!
 
Fantastic, good job! ... easy enough.

Maybe you can save me some time searching on google on how to make this formula go to the last row in the table using offset, count or other . We'll be adding approx. 2000 rows each week and I would prefer not to have to change formula every week. Again the formula is the last column in my table.

Thanks again in advance
 
Make your table (including the extra 2 columns) into a proper Excel Table (ribbon:Insert|Tables|Table). Then you can add the equivalent formulae (cells I2 and J2?):
Code:
=IF(ROW()=MIN(IF(([GRADE]=[@GRADE])*([SPECIES]=[@SPECIES])*([THICK]=[@THICK])=1,ROW([MSQ]))),SUMIFS([MSQ],[GRADE],[@GRADE],[SPECIES],[@SPECIES],[THICK],[@THICK]),"")
(don't forget to array-enter that one)
and:
Code:
=[@[Actual on hand]]/SUMIFS([MSQ],[GRADE],[@GRADE],[SPECIES],[@SPECIES],[THICK],[@THICK])
and they propagate themselves downwards.
When you add rows, make sure they're included within the table at the bottom (if you add/paste them at the bottom of the table that is usually what happens automatically). You'll have the added advantage that the pivot table range will automatically adjsut itself to the whole table if its Source range appears like Table1.
The only thing I'd be fearful of is the time to recalculate the table as it grows.
 
Yes ... this I found was true, they do propagate. When adding 3000 rows and calculating 5 columns of formulas it takes approx. 10 seconds to update.

SUMIFS ... What an awesome function. Just added another arrow in my quiver of excel tricks.

Thank you for all the help!
 
Back
Top