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

	A	B	C
1	Customer	Product	Quantity
2	Bill	Keyboard	1
3	John	Computer	1
4	Sonita	Mouse	2
5	Mike	Computer	1
6	Grace	Mouse	2
7	Tom	Phone	1
8	Bob	Phone	1
9	Jeb	Keyboard	2
10	Tim	Mouse	3

<tbody>
</tbody>

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

	A	B	C	D
1	Customer	Product	Quantity	Total Product Reserved
2	Bill	Keyboard	1	3
3	John	Computer	1	2
4	Sonita	Mouse	2	7
5	Mike	Computer	1	2
6	Grace	Mouse	2	7
7	Tom	Phone	1	2
8	Bob	Phone	1	2
9	Jeb	Keyboard	2	3
10	Tim	Mouse	3	7

<tbody>
</tbody>

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

	A	B	C	D
1	Product	Current Stock	Due Date	Reserves
2	Keyboard	0	16-Sep-18	3
3	Headset	7
4	Webcam	9
5	Mouse	0	12-Mar-18	7
6	Computer	0	09-Feb-18	2
7	Scanner	11
8	Printer	6
9	Phone	0	14-Apr-18	2
10	USB cable	12
11	Plug	19

<tbody>
</tbody>

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