Return a specific sum through duplicate list entries

moccasinus

New Member
Joined
Aug 8, 2017
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi, is there a way, without using Pivot, to return a specific sum through duplicate list entries, example:
Item
Qty
Qty needed
Total qty needed
Item#1
1
=
100
Item#1
200
=
Item#1
50
=
Item#1500
=

<tbody>
</tbody>


I only need the sum of 100 taken from 'Total qty needed' and returned in 'Qty needed' column based on corresponding values in column 'Qty'.
I imagine if there is a formula it should return such values:
Item
Qty
Qty needed
Total qty needed
Item#1
1
1
100
Item#1
200
99
Item#1
50
0
Item#1
500
0

<tbody>
</tbody>

Btw, the list I want to do this for is several thousand rows, would be great to avoid array formula.
Anyone knows what could be the formula for this? Thanks in advance
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Assuming that your data sample starts in A1 with no merged cells. Total quantity in E2.

=MIN(B2,MAX(0,$E$2-SUM(C$1:C1)))

For mixed items in column A, you would most likely need to use sumifs instead of sum.
 
Upvote 0
I can see already that this will work, thanks!
Can you please elaborate more on -SUM(C$1:C1) ? What sum is this supposed to be? The sum of already returned values for Item#1?
And how would SUMIF look if there's mixed items? Will the same formula work dragged down for mixed items?
Item
Qty
Qty needed
Total qty need
Item#1
1
Item#1
100
Item#1
200
Item#2
100
Item#1
50
Item#1
500
Item#2
75
Item#2
125
Item#2
225

<tbody>
</tbody>
 
Upvote 0
Can you please elaborate more on -SUM(C$1:C1) ? What sum is this supposed to be? The sum of already returned values for Item#1?
That is correct, it keeps a running total that stops at the row above the formula.

For a sumifs version (item # in E2:E3, total qty in F2:F3).

=MIN(B2,MAX(0,VLOOKUP(B2,$E$2,$F$3,2,0)-SUMIFS(C$1:C1,A$1:A1,A2)))

Note that with a lot of data, this could be slow to calculate. If your data is sorted by item # then there could be ways to make it a bit faster, for example

=IF(AND(A2=A1,C1=0),0,MIN(B2,MAX(0,VLOOKUP(B2,$E$2,$F$3,2,0)-SUMIFS(C$1:C1,A$1:A1,A2))))

would elimintate the slow part of the calculation where the item # is the same as the previous row and the previous row already has a 0 total.
 
Upvote 0
The refernce to B2 in the VLOOKUP should have been A2, that is probably the cause of the circular ref error.

=IF(AND(A2=A1,C1=0),0,MIN(B2,MAX(0,VLOOKUP(A2,$E$2,$F$3,2,0)-SUMIFS(C$1:C1,A$1:A1,A2))))
 
Upvote 0
I've noticed that, but it's the -SUMIFS, because it references to the cell the formula is in :(
Can't think of a workaround for it, if it works this way, there must be a away to avoid that as well
 
Upvote 0
I've noticed that, but it's the -SUMIFS, because it references to the cell the formula is in :(
Can't think of a workaround for it, if it works this way, there must be a away to avoid that as well

Try
A
B
C
D
E
F
1
Item#1​
1​
1​
Total qty need​
2
Item#1​
200​
99​
Item#1​
100​
3
Item#1​
50​
0​
Item#2​
100​
4
Item#1​
500​
0​
5
Item#2​
75​
75​
6
Item#2​
125​
25​
7
Item#2​
225​
0​

<tbody>
</tbody>


Formula in C1 copied down
=MAX(0,MIN(B1,VLOOKUP(A1,E:F,2,0)-SUMIF(A$1:A1,A1,B$1:B1)+B1))

M.
 
Last edited:
Upvote 0
I've noticed that, but it's the -SUMIFS, because it references to the cell the formula is in :(
No, it doesn't. The formula refernces the cell above, not the cell it is in. The formula referencing C$1:C1 should be in C2.

edit:- To clarify, the first formula looks at the headers above the data.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top