Formula for total products reserved

[MYMunshi]

Hi everyone

I'm having some difficulty deriving a formula for this problem.

Basically, a shop owner runs a hardware store. Sadly, some items are out of stock, so he decides to create a 'reserves' list. Customers approach the owner and create reservations. However, as some customers reserve of only one item, some customers reserve more than one of the same item, as shown in the table below.


RESERVES
[TABLE="width: 288"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Bill[/TD]
[TD]Keyboard[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]John[/TD]
[TD]Computer[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Sonita[/TD]
[TD]Mouse[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Mike[/TD]
[TD]Computer[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Grace[/TD]
[TD]Mouse[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Tom[/TD]
[TD]Phone[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Bob[/TD]
[TD]Phone[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Jeb[/TD]
[TD]Keyboard[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Tim[/TD]
[TD]Mouse[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]


Although a COUNTIF formula would be useful in this case to count the number of times a product has occurred, it wouldn't do the job in the sense of also counting the multiple times those products have been reserved.

So essentially, we're supposed to have another column that reads:


RESERVES
[TABLE="width: 441"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]Total Product Reserved[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Bill[/TD]
[TD]Keyboard[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]John[/TD]
[TD]Computer[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Sonita[/TD]
[TD]Mouse[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Mike[/TD]
[TD]Computer[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Grace[/TD]
[TD]Mouse[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Tom[/TD]
[TD]Phone[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Bob[/TD]
[TD]Phone[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Jeb[/TD]
[TD]Keyboard[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Tim[/TD]
[TD]Mouse[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]


Is there a formula please I could have help on for column D?

The reason I am doing this is because I also have a stock sheet (in a separate tab in the same sheet) with all other products on and I want to link reserves (in a separate tab) into that sheet. In the far right column of the Stock sheet, there will be a reserved column which adapts information from the Reserves column:


STOCK SHEET
[TABLE="width: 478"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Product[/TD]
[TD]Current Stock[/TD]
[TD]Due Date[/TD]
[TD]Reserves[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Keyboard[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16-Sep-18[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Headset[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Webcam[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Mouse[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12-Mar-18[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Computer[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]09-Feb-18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Scanner[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Printer[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Phone[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14-Apr-18[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]USB cable[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Plug[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I think for this second problem, the formula may involve extracting the maximum number of reserved products, but could you please help me derive a formula for this second problem as well please.

Thank you, greatly appreciated

 

[Matty]

Hi,

For the first problem, try:

=SUMIF(B$2:B$10,B2,C$2:C$10)

In D2, copied down.

Matty

 

[Matty]

Actually, the formula could be entered exclusively on your 'Stock Sheet' - no need to have it on your 'Reserves' sheet.

If reserves should only appear where current stock is 0, you could wrap the SUMIF within an IF to handle this.

Matty

 

[MYMunshi]

Actually, the formula could be entered exclusively on your 'Stock Sheet' - no need to have it on your 'Reserves' sheet.

If reserves should only appear where current stock is 0, you could wrap the SUMIF within an IF to handle this.

Matty

Hi Matty

Oh yeah! Great idea, keep it all in the Stock Sheet.

That solved the problem Matty, thanks so much for your help, greatly appreciated mate

 

[Matty]

Hi Matty

Oh yeah! Great idea, keep it all in the Stock Sheet.

That solved the problem Matty, thanks so much for your help, greatly appreciated mate

Glad to help!

Matty