+ Reply to Thread
Results 1 to 18 of 18

How can you look up how much each category has made into the category column? Formula?

  1. #1
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    How can you look up how much each category has made into the category column? Formula?

    How can you look up how much each category has made into the category column using a formula?
    Can a formula identify the category within the item number?

    Example:

    item number: GW14SO0000003
    item category: GW14SO
    item counter: 0000003


    mockup.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,169

    Re: How can you look up how much each category has made into the category column? Formula?

    with a pivottable you don't need formulas and is very simple.

  3. #3
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: How can you look up how much each category has made into the category column? Formula?

    I dont think I can use a pivot table for this. Did you open the mock up file?

  4. #4
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,169

    Re: How can you look up how much each category has made into the category column? Formula?

    with little effort (in excel2003)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: How can you look up how much each category has made into the category column? Formula?

    How did you get column I to only select the categories?

  6. #6
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: How can you look up how much each category has made into the category column? Formula?

    Is this method automated? When I add new rows of information, the pivot table does not update automatically?

  7. #7
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,169

    Re: How can you look up how much each category has made into the category column? Formula?

    you have to update the pivottable manually or you can write a macro to do so.

  8. #8
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: How can you look up how much each category has made into the category column? Formula?

    Quote Originally Posted by bsalv View Post
    you have to update the pivottable manually or you can write a macro to do so.
    When I double click the totals in the pivot table, why does a new sheet get created?

  9. #9
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: How can you look up how much each category has made into the category column? Formula?

    What were the steps you used to create the pivot table? I cant seem to get it right

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How can you look up how much each category has made into the category column? Formula?

    You can do it with formulas

    Use this in C29 of your mock-up file to get unique categories

    =INDEX(LEFT($D$6:$D$25,6),MATCH(0,INDEX(COUNTIF($C$28:C28,LEFT($D$6:$D$25,6)),,),))

    and drag formula down.

    And this one for calculations in D29

    =SUMPRODUCT((LEFT($D$6:$D$25,6)=$C29)*(F$6:F$25))

    then down and across

    C
    D
    E
    F
    28
    Category
    Total Purchases
    Sold
    Profit
    29
    GW30BF
    $5.00
    $43.00
    $48.00
    30
    GW37VS
    $4.00
    $26.00
    $28.00
    31
    GW14SO
    $1.00
    $7.00
    $8.00
    32
    GW67TZ
    $7.00
    $93.00
    $98.00
    33
    GW34PD
    $3.00
    $69.00
    $72.00
    Last edited by AlKey; 12-29-2013 at 10:33 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  11. #11
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: How can you look up how much each category has made into the category column? Formula?

    Wow, life saver. I was up all last night trying to figure this out. I was trying to use If ISerror Search, but had not luck combing formulas, maybe I was going the whole wrong way about the arguments.

    Where in the formula does it tell you to identify the category, example: GW30BF
    =INDEX(LEFT($D$6:$D$25,6),MATCH(0,INDEX(COUNTIF($C$28:C28,LEFT($D$6:$D$25,6)),,),))

  12. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How can you look up how much each category has made into the category column? Formula?

    This formula extracts first 6 characters from the unique list (LEFT($D$6:$D$25,6)

    The sumproduct first part is comparing it and uses as a criteria.

    Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  13. #13
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: How can you look up how much each category has made into the category column? Formula?

    Quote Originally Posted by AlKey View Post
    This formula extracts first 6 characters from the unique list (LEFT($D$6:$D$25,6)

    The sumproduct first part is comparing it and uses as a criteria.

    Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

    How can I add monthy sales to the new table created in the revised mockup workbook? thanks

    mockup.xlsx
    Last edited by matt323; 12-29-2013 at 05:07 PM.

  14. #14
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How can you look up how much each category has made into the category column? Formula?

    What date should be use Sell Date?

  15. #15
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How can you look up how much each category has made into the category column? Formula?

    In H29 across and down

    =SUMPRODUCT((LEFT($D$6:$D$25,6)=$G29)*(TEXT($C$6:$C$25,"mmmm")=H$28)*($G$6:$G$25))
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Re: How can you look up how much each category has made into the category column? Formula?

    This is more of a personal preference, how can I tell the formula to avoid adding the symbol "-" in the cells to avoid getting a #value! as a result. I revised the workbook below.

    mockup_alkey.xlsx

  17. #17
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How can you look up how much each category has made into the category column? Formula?

    This will replace "-" with 0

    For the Total Purchases

    =SUMPRODUCT((LEFT($D$6:$D$25,6)=$B29)*(SUBSTITUTE(F$6:F$25,"-",0)))


    For the Monthly

    =SUMPRODUCT((LEFT($D$6:$D$25,6)=$G29)*(TEXT($C$6:$C$25,"mmmm")=H$28)*(SUBSTITUTE($G$6:$G$25,"-",0)))

  18. #18
    Registered User
    Join Date
    09-29-2013
    Location
    LA
    MS-Off Ver
    Excel 2008
    Posts
    83

    Thumbs up Re: How can you look up how much each category has made into the category column? Formula?

    Awesome, now just 1 more question. How would I add weekly sales in the weekly sales section in the revised mockup workbook?

    mockup_alkey1.xlsx

+ 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. How to Chart values for Division vs Category and Sub-Category?
    By rahulgk in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-11-2013, 06:33 AM
  2. Filtering by text
    By saltarazan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-16-2013, 04:18 AM
  3. Replies: 2
    Last Post: 09-14-2012, 04:31 AM
  4. [SOLVED] Productsum: Highest Frequency Category, Specific Main Category, Frequency of Category
    By T86157 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2012, 12:43 PM
  5. Single field fits fault category, event category determined by group of faults
    By SchoobsVT in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-07-2010, 08:51 AM

Tags for this Thread

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