+ Reply to Thread
Results 1 to 5 of 5

Sum numbers based on a category of a category

Hybrid View

  1. 08-09-2012, 10:02 PM #1
    mattjac
    mattjac is offline
    Registered User
    Join Date
    08-10-2010
    Location
    Seattle
    MS-Off Ver
    Excel 2003
    Posts
    3

    Sum numbers based on a category of a category

    Hi, I really wanted to try to figure this out on my own but I am getting frustrated lol

    I have categories which all transactions fall under. However I don't like these categories (too many) so I created master categories holding them. I want to do a SUMIF type statement for each of the master categories, which would each hold multiple categories. I would do a SUMIFS statement, criteria being category 1, 2, 3 for master category 1 except that I have just a ridiculous amount of data - and each master category might be holding 20+ subcategories.

    Does anyone know how to sum up the various transactions based on the master categories???

    Thanks so much!

    Excel Problem.xls
    Reply With QuoteRegister To Reply
  2. 08-09-2012, 10:21 PM #2
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Sum numbers based on a category of a category

    from your sample file can you try this one
    for master category 1

    =SUMPRODUCT(SUMIF($C$1:$C$10,"*"&$B$15:$B$17&"*",$A$1:$A$10))

    for master category 2

    =SUMPRODUCT(SUMIF($C$1:$C$10,"*"&$B$18:$B$20&"*",$A$1:$A$10))

    let me know if this will work for you. thanks.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  3. 08-09-2012, 10:40 PM #3
    mattjac
    mattjac is offline
    Registered User
    Join Date
    08-10-2010
    Location
    Seattle
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Sum numbers based on a category of a category

    Wow thank you that's great! I think I manipulate the criteria (B15:B17) based on =cell(address) and =indirect

    Can you tell me what "*" does? Does that change "Category 1" to a number by any chance?

    Also I thought I knew how sumproduct worked - but it doesn't seem like anything is being multiplied here? Just grouping and then simple adding?
    Reply With QuoteRegister To Reply
  4. 08-09-2012, 10:44 PM #4
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Sum numbers based on a category of a category

    you can get rid it

    just assumed if there is an error Like category 1as
    Reply With QuoteRegister To Reply
  5. 08-09-2012, 11:08 PM #5
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Sum numbers based on a category of a category

    thanks for the rep.
    if you don't have any queries regarding above can you mark this thread "Solved".
    look at my signature on the steps.
    thanks again.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1