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

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)

3. Re: Sum Prices with Criteria from Two Lists

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

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:

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

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

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?

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:

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. 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

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

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

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