# Formula to multiply total number per category with the % of total

1. ## Formula to multiply total number per category with the % of total

Hello,

I'm looking for a formula that can help me figure out number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet. I attached the sample and hope you understand what I'm trying to achieve. Please advise the expected result spreadsheet.Test Book.xlsx

Thanks  Register To Reply

2. ## Re: Formula to multiply total number per category with the % of total

Hi Tantcu,

Put this formula in E4 then fill down and to the right:

=\$D4*INDEX('Category Summary'!\$C\$5:\$F\$11,MATCH(\$C4,'Category Summary'!\$A\$5:\$A\$11,0),MATCH(E\$3,'Category Summary'!\$C\$4:\$F\$4,0))

Hope that helps!  Register To Reply

3. ## Re: Formula to multiply total number per category with the % of total

=INDEX('Category Summary'!\$C\$6:\$F\$9,MATCH('Item Detail'!\$C4,'Category Summary'!\$A\$6:\$A\$9,0),MATCH('Item Detail'!E\$3,'Category Summary'!\$C\$4:\$F\$4,0))

will look up the value
and then
multiply by the percent
=INDEX('Category Summary'!\$C\$6:\$F\$9,MATCH('Item Detail'!\$C4,'Category Summary'!\$A\$6:\$A\$9,0),MATCH('Item Detail'!E\$3,'Category Summary'!\$C\$4:\$F\$4,0))*\$D4

note i removed the empty rows in the reference sheet

see attached  Register To Reply