+ Reply to Thread
Results 1 to 22 of 22

SUMIF based on the multiple criteria

  1. #1
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    SUMIF based on the multiple criteria

    Hi all,

    I'm trying to sum specific columns of table based on the values of 2 other cells.

    This is basically what I'm trying to accomplish:

    IF($D$1="LOW"), =SUMIF(DATA[BR],L19,DATA[PRICELOW])
    IF($D$1="MEDIUM"), =SUMIF(DATA[BR],L19,DATA[PRICEMED])
    IF($D$1="HIGH"), =SUMIF(DATA[BR],L19,DATA[PRICEHIGH])

    Is there a way to put this all into one formula?

    Thanks!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: SUMIF based on the multiple criteria

    I can't tell what you are trying to accomplish but usually you write a sumif to handle the if then sum. So like =SUMIF(DATA[BR],"LOW",[RANGE TO SUM]) or =SUMIF(DATA[BR],$D$1,[RANGE TO SUM])
    and the last one will allow you to use D1 as what you need in one formula.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: SUMIF based on the multiple criteria

    Dunno if this will work, try it
    Assuming D1 can only be LOW MEDIUM or HIGH and nothing else

    =SUMIF(DATA([BR],L19,DATA[INDIRECT("PRICE"&$D$1])
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    Try

    =IF($D$1="LOW",SUMIF(DATA[BR],L19,DATA[PRICELOW]),IF($D$1="MEDIUM",SUMIF(DATA[BR],L19,DATA[PRICEMED]),SUMIF(DATA[BR],L19,DATA[PRICEHIGH])))

    assumes only these 3 conditions
    Last edited by JohnTopley; 01-13-2016 at 11:25 AM.

  5. #5
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    I have a combo box with "LOW","MEDIUM", AND "HIGH" which feeds to cell $D$1. I have a separate tab that houses my data. If $D$1 says "LOW", I want excel to lookup the BR column on my table, find the BR criteria (see below), and provide a SUM of the PRICELOW column for that specific unit type

    Unit Type
    1BR
    2BR
    3BR
    4BR
    5BR
    7BR

  6. #6
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    That worked perfect John. THANKS!

  7. #7
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    my next question would be is how do write a MIN and MAX formula for the Price column that is linked to "LOW,"MEDIUM","HIGH" columns?

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    No problem: thanks for the feedback.

    If your query has been answered, could you mark thread as SOLVED ("Thread Tools" at top of first post).

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    Jumped the gun! Not sure what you mean by MIN/MAX related to the columns.

    =MAX(Data[PRICELOW]) ?

  10. #10
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    Looking for the lowest/highest price in the PRICELOW column if $D$1="LOW", Looking for the lowest/highest price in the PRICEMED column if $D$1="MEDIUM",Looking for the lowest/highest price in the PRICEHIGH column if $D$1="HIGH"

  11. #11
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    Sorry, and for the specific BR Type as well

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    Something like ..??

    =IF($D$1="LOW",MIN(DATA[PRICELOW]),IF($D$1="MEDIUM",MIN(DATA[PRICEMED]),MIN(DATA[PRICEHIGH])))

    Replace MIN with MAX

  13. #13
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    I also meant to add in the additional criteria as well.
    Basically looking for the MIN/MAX of this sum formula you gave me

    =IF($D$1="LOW",SUMIF(DATA[BR],L19,DATA[PRICELOW]),IF($D$1="MEDIUM",SUMIF(DATA[BR],L19,DATA[PRICEMED]),SUMIF(DATA[BR],L19,DATA[PRICEHIGH])))

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    You will need to use INDEX to identify the column of data

    Could you post a sample showing expected results as it easier to work with some data.

  15. #15
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    I'm able to use this formula to look up the size of the unit because that is not effected by the Low,Med,High criteria
    =MIN(IF(DATA[BR]=F98,DATA[SF]))
    So I need to add that additional criteria in




    Price Range SF Range $PSF Range
    $0 $0 450 956 $0 $0
    $0 $0 1,422 2,170 $0 $0
    $0 $0 1,857 2,466 $0 $0
    $0 $0 2,193 3,901 $0 $0
    $0 $0 2,534 4,479 $0 $0
    $0 $0 4,463 5,087 $0 $0

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    If there are other criteria, try

    =MIN(IF((DATA[BR]=F98)*(criteria2 ),DATA[SF]))

    the "*" acts as an "AND" condition i.e. both are true

    or

    =MIN(IF(DATA[BR]=F98,IF(criteria2,DATA[SF]),""),"")

  17. #17
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    cant get to work

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    Please post a sample file: without seeing the data we are working in the dark.

  19. #19
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    File attached
    Attached Files Attached Files

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    Formula used

    =MIN(IF(DATA!$D$2:$D$89=STACK!$D4,INDEX(DATA!$A$2:$S$89,,MATCH("PRICE"&A$1,DATA!$A$1:$S$1,0))))

    Entered with Ctrl+ShifT+Enter

    See attached
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    01-12-2016
    Location
    New York
    MS-Off Ver
    2015
    Posts
    76

    Re: SUMIF based on the multiple criteria

    works perfect, Thanks!

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUMIF based on the multiple criteria

    If all questions have been addressed, could you please mark thread as SOLVED ("Thread Tools" at top of first post).

    Thank you.

+ 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. SUMIF with VLOOKUP based on multiple criteria
    By Oscady in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-01-2015, 10:58 AM
  2. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  3. Sumif based on multiple criteria
    By SUPERFLY30 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2011, 09:25 AM
  4. SUMIF based on multiple date criteria
    By titantough in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2011, 02:54 PM
  5. Sum based on multiple criteria (sumif)
    By dubjones in forum Excel General
    Replies: 2
    Last Post: 04-27-2010, 10:10 AM
  6. SUMIF with multiple criteria, one criteria a specific string
    By Weasel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2008, 05:32 PM
  7. SUMIF in multiple columns based on other criteria in Excel?
    By Scott Powell in forum Excel General
    Replies: 9
    Last Post: 04-13-2005, 10:06 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