+ Reply to Thread
Results 1 to 6 of 6

SUMIFS (Several Critera)

  1. #1
    Registered User
    Join Date
    01-19-2015
    Location
    Dubai
    MS-Off Ver
    MS Office 2013
    Posts
    11

    SUMIFS (Several Critera)

    Hi There,

    I am trying to sum the values from one range of cells depending on several criteria from one sheet to another. I am trying the SUMIFS, not sure if this is the best.

    I want to sum the revenue of all "AIR" shipments with a weight between 300 to 500.

    Quite basic probably, but I am really stuck here. I attach a sample in excel also, where the result should be 300 in revenue. Might be easier perhaps seeing the excel doc itself.

    Thanks and Best regards,

    Mackan7695
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,829

    Re: SUMIFS (Several Critera)

    Please Login or Register  to view this content.
    You threw me however with your expected result, the correct answer is 100 not 300. Only row 8 contains "AIR" and a weight between 300 and 500
    If someone has helped you then please add to their Reputation

  3. #3
    Registered User
    Join Date
    01-19-2015
    Location
    Dubai
    MS-Off Ver
    MS Office 2013
    Posts
    11

    Re: SUMIFS (Several Critera)

    Haha...yes. I my mistake. I did it a bit too quickly...

    Thank you for a quick reply!

    //M

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: SUMIFS (Several Critera)

    OR,

    Try the following:

    =SUMPRODUCT(--(A1:A10="AIR")*(B1:B10>=300)*(B1:B10<=500),C1:C10)

  5. #5
    Registered User
    Join Date
    01-19-2015
    Location
    Dubai
    MS-Off Ver
    MS Office 2013
    Posts
    11

    Re: SUMIFS (Several Critera)

    Excellent...thanks.

    //M

  6. #6
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: SUMIFS (Several Critera)

    You are welcome

    If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

    Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.

+ 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] SUMIFS, COUNTIFS, Using a date as one of the critera
    By fgruhlke in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2014, 10:12 PM
  2. [SOLVED] SUMIFS with <> Critera
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2013, 01:35 PM
  3. [SOLVED] Returning sum based on row and column critera (SUMIFS??)
    By PERE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2013, 11:55 PM
  4. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  5. [SOLVED] Sumifs, problem with the formula: =sumifs(c10:c200,<=today(),0)
    By Faustocruz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-08-2012, 04:26 AM
  6. Vlookup (3 critera)
    By Engineers08 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-23-2008, 11:44 AM
  7. Add two critera containing dates
    By Eyeballs in forum Excel General
    Replies: 3
    Last Post: 12-06-2007, 04:06 PM
  8. How do I sum with two critera?
    By Clare in forum Excel General
    Replies: 4
    Last Post: 08-24-2005, 10:05 AM

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