Formula to Calculate a $ Value per Unique Part# with Multiple Warehouse Locations

Current Spread sheet structure: Multiple Columns ("A" through "U") - 10,000+ rows
Goal: Formula to calculate the Unallocated $ Value of Inventory (once per unique Item #)
Dollar result to be shown in Column "U" - Row 2 (ability to copy down all rows)
Primary Columns for reference:
"A" - Item # (Number) - This can appear on multiple rows for multiple warehouse locations
"F" - Location (Warehouse) - would only like to have Location "1" and Location "2" included
"G" - Quantity on Hand
"I" - Cost per Unit
"K" - Quantity needed for Production
"M" - Balance - currently a calculated column = ("G" minus "K")
"N" - Revised $ Value - currently a calculated field = ("M" times "I")
"U" - Unallocated $ Value (goal formula) - once per unique Item # in Column "A"
My current formula in Column "U" (then copied down) is =IF(G2>K2,N2,0)
This was sufficient when all "G" Quantity on Hand was in "F" Location 1 only
Now a combination of "G" Quantity on Hand needs to be both "F" Location 1 and Location 2
Any assistance would be greatly appreciated - Thank you.

This ??

=IF(SUMIF($A$2:$A$24,$A$2,$K$2:$K$24)>SUMIF($A$2:$A$24,A2,$G$2:$G$24),0,"")

John: Cheers! Thank you for the quick response - I will test and return a reply soonest.

Try this in U2 and copy down:

=IF(A2=A1,"",IF(SUM(SUMIFS($G$2:$G$24,$F$2:$F$24,{1,2},$A$2:$A$24,A2))>SUM(SUMIFS($K$2:$K$24,$F$2:$F$24,{1,2},$A$2:$A$24,A2)),SUM(SUMIFS($N$2:$N$24,$F$2:$F$24,{1,2},$A$2:$A$24,A2)),0))

Dave, do you realise that all the answers are zeroes because "Total Qty on Hand" are less than "Total Qty Needed" in your sample workbook?

Another option: IF(COUNTIF($A$2:$A2,A2)=1,MAX(0,SUMPRODUCT(($G$2:$G$24-$K$2:$K$24)*($A$2:$A$24=A2)*$I$2:$I$24)),"")

=if(k2=0,"",if(sumif($a$2:$a$24,$a$2,$k$2:$k$24)>sumif($a$2:$a$24,a2,$g$2:$g$24),0,""))

Menem: Thank you for your efforts. I've tested and found the following:
Your noted this result is just the quantity - you are correct. The next calculation would be to take that quantity and multiply by the Unit cost (which is currently consistent for all locations)
I'd like that result (Qty * Unit Cost to appear only once per unique Item number. - Currently it appears in column U for each Location (thus increasing the desired result $)

Appreciate the dialog - and your time. Dave

Did you try my formula in post#6 above? Did it give the wrong answers?

Greg: I've received your response - Thank you kindly: For Speed and memory / CPU usage, this works very nicely.

As josephteh notes: My original sample attachment may not have given a complete understanding of the possible results.
I am attaching a MODIFIED file to this reply (hope I'm doing it correctly).

1. If the total QOH per Item is less than the total Quantity Needed per item, then return $0.00
2. If the total QOH per Item is Greater than the total Quantity Needed per item, then return ((Total QOH - Total Quantity Needed)*Unit Cost)

Hoping this helps - I'm excited to see such a response! Dave

Greg: I've replied with a bit of additional information (and a slightly modified attachment to better illustrate the challenge)
Thank you, Dave

josephteh: I'm realizing that now (My attachment may have been insufficient to fully illustrate the complete goal) - thank you for point that out. I've posted a slightly modified file to this reply.

Summary Goal:
1. If the total QOH per Item is less than the total Quantity Needed per item, then return $0.00
2. If the total QOH per Item is Greater than the total Quantity Needed per item, then return ((Total QOH - Total Quantity Needed)*Unit Cost)

Thank you so much for your effort. Dave

John:

As josephteh notes: My original sample attachment may not have given a complete understanding of the possible results.
I am attaching a MODIFIED file to this reply (hope I'm doing it correctly).

1. If the total QOH per Item is less than the total Quantity Needed per item, then return $0.00
2. If the total QOH per Item is Greater than the total Quantity Needed per item, then return ((Total QOH - Total Quantity Needed)*Unit Cost)

Dave,
My original formula in post #6 gives the same answers as your most recent file posted. Is there a sample where it gives the wrong answer?
(you don't have to create a reply for each contributor, we'll read any post).

As per #16

=IF($K2=0,0,MAX(SUMIF($A$2:$A$24,$A2,$G$2:$G$24)-SUMIF($A$2:$A$24,$A2,$K$2:$K$24),0))*$I2

Greg:

Current try: =IF(A2=A1,"",IF(SUM(SUMIFS($G$2:$G$24,$F$2:$F$24,{1,2},$A$2:$A$24,A2))>SUM(SUMIFS($K$2:$K$24,$F$2:$F$24,{1,2},$A$2:$A$24,A2)),SUM(SUMIFS($N$2:$N$24,$F$2:$F$24,{1,2},$A$2:$A$24,A2)),0))

My working spread sheet currently has nearly 70,000 rows (and could have more in the future).
The above formula is returning $0.00 result for all Item Numbers through Row ~70,000.

Perhaps the references to $24 in the above formula is limiting the overall analysis range?

Thank you

Yes, of course that's limiting it. It was just referencing your sample file. 2 things:
1. You can try this and it won't matter how many rows you have:
=IF(A2=A1,"",IF(SUM(SUMIFS($G:$G,$F:$F,{1,2},$A:$A,A2))>SUM(SUMIFS($K:$K,$F:$F,{1,2},$A:$A,A2)),SUM(SUMIFS($N:$N,$F:$F,{1,2},$A:$A,A2)),0))

2. The other option would be to put your data into an Excel Table, and then reference the Excel Table Column

Greg: This is working nicely - thank you all so very much for your assistance.

Any thoughts on a "tweak" to conserve a bit of CPU consumption? (It only lasts for about 4-5 minutes).

I believe we can close this issue as solved. Cheers!

Any thoughts on a "tweak" to conserve a bit of CPU consumption? (It only lasts for about 4-5 minutes).

Please see my option #2 in post #19.

Sample attached.

Greg: Understood - Thanks again for your assistance - Much appreciated.

Good Morning: Option #1 in Post 19 was working flawlessly *until some additional warehouse locations were added to the requirement*

1. You can try this and it won't matter how many rows you have:
=IF(A2=A1,"",IF(SUM(SUMIFS($G:$G,$F:$F,{1,2},$A:$A,A2))>SUM(SUMIFS($K:$K,$F:$F,{1,2},$A:$A,A2)),SUM(SUMIFS($N:$N,$F:$F,{1,2},$A:$A,A2)),0))

This formula was originally based upon the requirement to only include two locations: "1" and "2". The requirement now is to analyze four locations: "1", "1R", "2" and "2R"
Any suggestions to modify? Thank you. Dave

Off the top of my head I would replace the {1,2} with {"1","2","1R","2R"}. (include the quotes). If that doesn't work, please attach a file and show the results you're expecting.

Yes Greg! Thank you (I was missing the quotes). Much Appreciated. Dave