If value in column A then add values in column b

[Timwhitcher]

I am trying to make a resource tracker with a front sheet and a data sheet. we can only order in boxes of 25 so spare stock builds up
The data sheet 2 looks similar to this with customer details that can be ignored

Installer Name	amount ordered	amount needed
Dave	25	20
Bob	50	36
Jane	75	67
Bob	25	12

i need the front page (Sheet 1) to look like this

	Dave	Bob	Jane
Total amount ordered	on sheet 2 if column 1 = Dave add values in column 2
Total amount needed	on sheet 2 if column 1 = Dave add values in column 3
Spare stock	=SUM(B2-B3)

how do i filter by the installer name to get the totals i need?

 

[jynxy]

Hi,

Think you would use something like

SUMPRODUCT(SUMIFS(B2:B5,A2:A5,{"name","name2"}))

A2:A5 is your installer name
B2:B5 is your amount ordered

"name","name2" is the names you want to lookup and count.

change B2:B5 to C2:C5 for amount needed.

 

[Fluff]

How about like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Installer Name	amount ordered	amount needed				Dave	Bob	Jane
2	Dave	25	20			Total amount ordered	25	75	75
3	Bob	50	36			Total amount needed	20	48	67
4	Jane	75	67			Spare stock	5	27	8
5	Bob	25	12
Main

Cell Formulas
Range	Formula
G2:I2	G2	=SUMIFS(B:B,A:A,G1:I1)
G3:I3	G3	=SUMIFS(C:C,A:A,G1:I1)
G4:I4	G4	=G2#-G3#
Dynamic array formulas.

 

[Timwhitcher]

How about like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Installer Name	amount ordered	amount needed				Dave	Bob	Jane
2	Dave	25	20			Total amount ordered	25	75	75
3	Bob	50	36			Total amount needed	20	48	67
4	Jane	75	67			Spare stock	5	27	8
5	Bob	25	12
Main

Cell Formulas
Range	Formula
G2:I2	G2	=SUMIFS(B:B,A:A,G1:I1)
G3:I3	G3	=SUMIFS(C:C,A:A,G1:I1)
G4:I4	G4	=G2#-G3#
Dynamic array formulas.

That is perfect..... thank you so much
just changed the column locations and it works a dream
formula now looks like.... =SUMIFS(Sheet2!D:D,Sheet2!C:C,B1:I1)

 

[Fluff]

You're welcome & thanks for the feedback.