SUM If row value is the same?

[AndyGray]

This might get a little bit misleading however I will try and be as clear as possible....
This below table represents Sheet 1.

Line Number	Stock
4001	10
4002	50
4003	250

<tbody>
</tbody>

Sheet 2 is linked to sheet 1 via Line number and the total stock needs to match up to stock on this table, but by a sum of each line.

Line Number	Batch	Stock
4001	75-B	10
4002	77-B	50
4003	69-D	50
4003	69-D	100
4003	72-B	100

<tbody>
</tbody>

In Sheet 2. I want to have an additional row called Total Stock which will automatically calculate the stock value of the line number. So Line number 4003 will bring back a value of 50+100+100.


this will then be used to do a true or false check on sheet 1 (does stock match?)

Any suggestions are welcome.

Thanks.

 

[FormR]

In Sheet 2. I want to have an additional row called Total Stock which will automatically calculate the stock value of the line number. So Line number 4003 will bring back a value of 50+100+100.

Hi, I don't know if you got your sheets mixed up, but wouldn't it be better to return the calculated stock to sheet 1 to do the check? i.e.

Excel 2013/2016
	A	B	C	D
1	Line Number	Stock	Total Stock	Check
2	4001	10	10	TRUE
3	4002	50	50	TRUE
4	4003	250	250	TRUE
5	4004	100	105	FALSE
Sheet1

Cell Formulas
Range	Formula
C2	=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2)
D2	=B2=C2

Excel 2013/2016
	A	B	C
1	Line Number	Batch	Stock
2	4001	75-B	10
3	4002	77-B	50
4	4003	69-D	50
5	4003	69-D	100
6	4003	72-B	100
7	4004	a	5
8	4004	b	100
Sheet2

 

[AndyGray]

Sheet 1 is data that can be easily exported from the current database i'm using. So i have counts currently in here of a specific value as it is a raw data export. This basically displays "Product/Warehouse/Stock" But each Stock line and warehouse line have a unique line (hence line number) Product A will be in warehouse 1 2 and 3. but will have a unique line for each one...

The new system we are moving over to requires batches to be put in (these are going to have to be manually inserted) When this count is getting inserted into sheet 2. I wanted the check to show up on this sheet so I can match up as the data is input. I've just looked into the SUMIF formula (thank you for that by the way)

=SUMIF($C$3:$C$444,C3,$N$3:$N$444)

So it is checking row C for values which are the same, then using the criteria for them values to bring back a total sum of them specific lines. Then my Vlookup is on the next row over...


Now i am able to do a check and say Does it match? Or i can even say "discrepancy difference" and tell me how much my count is out by so i can adjust accordingly.

Thanks for your help.

 

[FormR]

Now i am able to do a check and say Does it match? Or i can even say "discrepancy difference" and tell me how much my count is out by so i can adjust accordingly.

I'm going to say "Yes"