LinkBack URL
About LinkBacks
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
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
Last edited by wharles62; 04-06-2020 at 08:18 PM.
In F2 =IF(E2>5000,SUMIFS($B$2:$B$101,$A$2:$A$101,D2),"") and copy down.
In H7 =SUM(F2:F21)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Please try
=SUMPRODUCT(SUMIFS(B2:B101,A2:A101,REPT(D2:D21,E2:E21>5000)))
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
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
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 AlanOriginally Posted by alansidman
Last edited by wharles62; 04-05-2020 at 07:48 PM.
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?
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.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
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.
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
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)
Posting Permissions
Register To Reply
Bookmarks