+ Reply to Thread
Results 1 to 10 of 10

Sum Prices with Criteria from Two Lists

  1. #1
    Registered User
    Join Date
    04-04-2020
    Location
    Surprise, AZ
    MS-Off Ver
    2010
    Posts
    8

    Sum Prices with Criteria from Two Lists

    I want the total price for all the items in Column A that are only listed in Column D AND its code is greater than 5000.

    What would be the formula for it? Please see the image below... I have also attached my excel file.

    Capture0.JPG

    -Charles
    Attached Files Attached Files
    Last edited by wharles62; 04-06-2020 at 08:18 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,974

    Re: Sum Prices with Criteria from Two Lists

    In F2 =IF(E2>5000,SUMIFS($B$2:$B$101,$A$2:$A$101,D2),"") and copy down.

    In H7 =SUM(F2:F21)

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,900

    Re: Sum Prices with Criteria from Two Lists

    Please try
    =SUMPRODUCT(SUMIFS(B2:B101,A2:A101,REPT(D2:D21,E2:E21>5000)))

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    Re: Sum Prices with Criteria from Two Lists

    Hi Wharles,

    This is the first time I've ever used a DSum() formula and it worked. If you sort the item list by code so you can use it as an Advanced Filter Criteria, your formula looks like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See the attached for Bo_ry's answer to check mine.
    DSum() formula for Wharles.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    04-04-2020
    Location
    Surprise, AZ
    MS-Off Ver
    2010
    Posts
    8

    Re: Sum Prices with Criteria from Two Lists

    Quote Originally Posted by alansidman View Post
    In F2 =IF(E2>5000,SUMIFS($B$2:$B$101,$A$2:$A$101,D2),"") and copy down.
    In H7 =SUM(F2:F21)
    Definitely works. I never thought of entering the function this way so I learned something here. I don't like having an extra column on the side for pre calculations but this will come in handy for future works. Thanks Alan
    Last edited by wharles62; 04-05-2020 at 07:48 PM.

  6. #6
    Registered User
    Join Date
    04-04-2020
    Location
    Surprise, AZ
    MS-Off Ver
    2010
    Posts
    8

    Re: Sum Prices with Criteria from Two Lists

    Quote Originally Posted by Bo_Ry View Post
    Please try
    =SUMPRODUCT(SUMIFS(B2:B101,A2:A101,REPT(D2:D21,E2:E21>5000)))
    Bo_Ry, I think you have the best formula so far. What I'm using is actually on a long list of stock prices, and this is just a small piece of the total formula. The longer the formulas are and the more array functions they contain, the slower the calculations take. Anyway we can shorten it up a bit?

  7. #7
    Registered User
    Join Date
    04-04-2020
    Location
    Surprise, AZ
    MS-Off Ver
    2010
    Posts
    8

    Re: Sum Prices with Criteria from Two Lists

    Quote Originally Posted by MarvinP View Post
    Hi Wharles,

    This is the first time I've ever used a DSum() formula and it worked. If you sort the item list by code so you can use it as an Advanced Filter Criteria, your formula looks like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See the attached for Bo_ry's answer to check mine.
    Attachment 670712
    Thanks Marvin--I've never used DSUM() either before but it really shortens up the formula. Now my laptop won't huff n puff whenever I enter something in the cells... which btw did Formulas > Calculation Options > Manual to perform faster. The only downside to this formula is having to sort the list by code and manually select the range >5000 because there can only be one criteria. It's a pain to do that each time I add something to the list.

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    Re: Sum Prices with Criteria from Two Lists

    Hi wharles,

    You could do an Advanced Filter to get all Item Codes you need and then do a DSum() that filtered list. Advanced Filter is very, very fast. If you did this a few times each day then learning and creating Dynamic Named Ranges might also help. See the attached where I've done 3 Dynamic Named Ranges and then look at the final cell formula. This might make your work faster but I'm not sure..
    DSum() with Adv Filter of code first.xlsx
    Last edited by MarvinP; 04-05-2020 at 10:01 PM.

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,900

    Re: Sum Prices with Criteria from Two Lists

    Array formula won't get any faster,
    Better use MarvinP's DSum method.

    I use MarvinP Sheet.

    Key your criteria 5000 in G2

    I2
    =IF(E2>$G$2,D2,"|") this is not L it can be any symbol that not in your Item / \ @ will do the job
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-04-2020
    Location
    Surprise, AZ
    MS-Off Ver
    2010
    Posts
    8

    Re: Sum Prices with Criteria from Two Lists

    I prefer a cleaner sheet where I don't have the extra columns for calculations (i.e. G, I and J), but it did process faster than the array so I'll go with this solution. Thanks guys

+ 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. Excel giving out prices, taking in consideration of criteria
    By backdoor in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-24-2017, 07:37 AM
  2. Excel giving out prices, taking in consideration of criteria.
    By backdoor in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-18-2017, 08:38 AM
  3. Creating active drop down lists, with prices and other variable
    By Christiansweaver in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-10-2017, 10:19 AM
  4. [SOLVED] Mahing prices vs. rate card with criteria
    By tis1337 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-18-2016, 05:04 AM
  5. [SOLVED] Extracting latest prices from a price book with past and present prices
    By benoj2005 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-10-2012, 04:08 AM
  6. Excel 2007 : How to return prices from multiple lists
    By fremarco in forum Excel General
    Replies: 6
    Last Post: 03-15-2012, 05:16 AM
  7. Use a multiplier to change List Prices to Net prices
    By Dangada in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 12:05 PM

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