+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2010
    Posts
    376

    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

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824

    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!

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,803

    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
    Attached Files Attached Files
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel formula needed: add up, then divide total by 60, then multiply by 22
    By nonyanon in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-01-2013, 06:45 PM
  2. Replies: 2
    Last Post: 04-18-2012, 08:08 PM
  3. Replies: 4
    Last Post: 01-21-2012, 02:56 AM
  4. Find total time taken by each person to process total number of letters.
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-07-2010, 10:04 AM
  5. multiply a number by percentages and round so that they add upto the total %
    By dittotharappel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2009, 01:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1