+ Reply to Thread
Results 1 to 7 of 7

Sum the value for things with multiple criterias

  1. #1
    Registered User
    Join Date
    10-09-2015
    Location
    Jakarta
    MS-Off Ver
    Excel 2013
    Posts
    3

    Sum the value for things with multiple criterias

    Hi there, I'm new here (so please be gentle )

    So I'm trying to create a calculator for a game I've been playing (very productive, I know) and I've reached the part where I have to calculate different ratings (damage, critical, etc) for each skill. Now each skill has various tags (combinations of Physical/Energy, Melee/Ranged, Area, etc) so I need to get the rating value for those tags and add them up.

    I've tried using things like DSUM but if a Tag column is empty, it somehow ended up adding all values instead of just the specific ones. Anyway, explaining it here would probably be long n unnecessary so here's my sample workbook

    Sample.xlsx

    Note: I managed to get what I wanted by using SUM and VLOOKUP but the resulting formula gets lengthy, so I'm wondering if there's a better, simpler way to do it.

    Thanks in advance for your help

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Sum the value for things with multiple criterias

    Try
    G3=SUMPRODUCT(($B3:$F3=STAT!$B$2:$B$9)*STAT!$C$2:$C$9)
    H3=SUMPRODUCT(($B3:$F3=STAT!$B$2:$B$9)*STAT!$C$11:$C$18)

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Sum the value for things with multiple criterias

    If you wanna dynamically then try
    G3=SUMPRODUCT(($B3:$F3=OFFSET(STAT!$B$1,MATCH(SKILL!G$2,STAT!$A$2:$A$18,0),0,COUNTIFS(STAT!$A$2:$A$18,SKILL!G$2),))*OFFSET(STAT!$A$1,MATCH(SKILL!G$2,STAT!$A$2:$A$18,0),2,COUNTIFS(STAT!$A$2:$A$18,SKILL!G$2),)) and drag over all yellow cell.

    ** You must unmerge the STAT sheet A column.

    Please check the attached sheet.
    Last edited by shukla.ankur281190; 10-09-2015 at 06:41 AM.

  4. #4
    Registered User
    Join Date
    10-09-2015
    Location
    Jakarta
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Sum the value for things with multiple criterias

    Quote Originally Posted by shukla.ankur281190 View Post
    Double post !!!!!!
    HOLY COW!! Tried it n it worked, still can't believe it's actually that simple Still has much to learn, this young Padawan

    Thank you so much

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Sum the value for things with multiple criterias

    If you will check the post #3 it will be best for your I hope

    Glad it worked

  6. #6
    Registered User
    Join Date
    10-09-2015
    Location
    Jakarta
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Sum the value for things with multiple criterias

    Quote Originally Posted by shukla.ankur281190 View Post
    If you will check the post #3 it will be best for your I hope

    Glad it worked
    LOL. The 3rd post is actually the kinda lengthy formula I was trying to avoid. But am curious, why is that one more 'dynamic'? What's the difference between those 2?

  7. #7
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Sum the value for things with multiple criterias

    Its a single formula. There would be no need to change any ranges. It can itself manage and extend ranges length according their need.

    A single formula posted in #3.

    Once have a look in attached file in post #3

+ 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. Replies: 0
    Last Post: 02-02-2014, 08:42 AM
  2. [SOLVED] Using Data tables when there are MULTIPLE things that can change
    By rikkyshh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-30-2013, 03:07 AM
  3. [SOLVED] Populate Values in Multiple List Boxes based on Multiple Criterias
    By s2jrchoi in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-09-2013, 11:39 AM
  4. Search for multiple things at once
    By MDW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2013, 11:44 PM
  5. [SOLVED] Single cell doing multiple things?
    By cause in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2013, 11:50 PM
  6. [SOLVED] Counting multiple things
    By babalurugby in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-03-2012, 09:55 AM
  7. Link multiple things into a single table
    By danielshillcock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2011, 08:10 AM
  8. Making a command(macro?) to do multiple things
    By rdubya in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-31-2007, 05:19 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