# Sum Prices with Criteria from Two Lists

1. ## 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  Register To Reply

2. ## 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)  Register To Reply

3. ## Re: Sum Prices with Criteria from Two Lists

=SUMPRODUCT(SUMIFS(B2:B101,A2:A101,REPT(D2:D21,E2:E21>5000)))  Register To Reply

4. ## 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:  `Please Login or Register  to view this content.`

See the attached for Bo_ry's answer to check mine.
DSum() formula for Wharles.xlsx  Register To Reply

5. ## Re: Sum Prices with Criteria from Two Lists Originally Posted by alansidman 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  Register To Reply

6. ## Re: Sum Prices with Criteria from Two Lists Originally Posted by Bo_Ry =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?  Register To Reply

7. ## Re: Sum Prices with Criteria from Two Lists Originally Posted by MarvinP 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:  `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.  Register To Reply

8. ## 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  Register To Reply

9. ## 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  Register To Reply

10. ## 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  Register To Reply

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