Sum based on criterias that belongs to a group? Excel file shared

[Camel123]

Hi,

Anyone who can help me solve the described issue please? Please see uploaded images in thread or download the excel file:

easyupload.io

easyupload.io

easyupload.io

In sheet 1,
col. B= machine numbers, col. C= machine hours spent

In sheet 2,
col. B= which machine group each machine number belong to, col. C= all machine numbers

I want a formula to do the following if "true"= if col.B (sheet 1) is equal to a machine group, then sum all the machine hours in col. C (sheet 1) for all machines that belong to that group, based on the grouping in sheet 2,.

 

[Eric W]

Both tables on the same sheet for convenience, but try:

Book2
	A	B	C	D	E	F
1
2		Machine	Hours		Machine Group	Machine
3		A1901	100		Grp1	A1901
4		A2032	200		Grp1	A2032
5		A3790	300		Grp1	A3790
6		B4516	400		Grp2	B4516
7		B3311	500		Grp2	B3311
8		Grp1	600
9		Grp2	900
10
Sheet5

Cell Formulas
Range	Formula
C8:C9	C8	=SUMPRODUCT($C$3:$C$7,--(COUNTIFS($E$3:$E$7,B8,$F$3:$F$7,$B$3:$B$7)>0))

You should be able to see how to change the ranges for your sheet.

 

[Camel123]

Both tables on the same sheet for convenience, but try:

Book2
	A	B	C	D	E	F
1
2		Machine	Hours		Machine Group	Machine
3		A1901	100		Grp1	A1901
4		A2032	200		Grp1	A2032
5		A3790	300		Grp1	A3790
6		B4516	400		Grp2	B4516
7		B3311	500		Grp2	B3311
8		Grp1	600
9		Grp2	900
10
Sheet5

Cell Formulas
Range	Formula
C8:C9	C8	=SUMPRODUCT($C$3:$C$7,--(COUNTIFS($E$3:$E$7,B8,$F$3:$F$7,$B$3:$B$7)>0))

You should be able to see how to change the ranges for your sheet.

Thank you, will try it tomorrow at work!