Sum of Cells By Font Color

[Tricky7985]

Hi all. I have been trying to get, obtain, and tried to get the cells in J4, J5,&J6 to SUM the totals of (manual input) into Cells C4:C9 + G4:G9 .

Pb (blue) is Lead
Zn (red) is Zinc
Cu (green) is Copper

I have downloaded and tried many formulas (no success, i'm not very good at this) What i am hoping for the totals to automatically populate in the "Total Loads" next to the corresponding (Product, ie: Pb as same font color Blue, Zn font color Red &Cu font color Green) all assistance will be appreciated, my brain or whats left of it aches.

Truck Forecast - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Monday	2/03/2020	D/S		Monday	2/03/2020	N/S
2
3	Trucks	Product	Loads		Trucks	Product	Loads		24hr	Total Loads		Pb
4	697	Pb	8		697	Zn	1		Pb			Zn
5	698	Cu	3		698	Pb	1		Zn			Cu
6	699	Pb	2		699	Cu	2		Cu			Workshop
7	700	Zn	9		700	Pb	1					Shift Change
8	701	Workshop			701	Zn	1
9	T533	Shift Change			T533	Shift Change
10									Pb to equal any value added into C4:C9 + G4:G9 (same font colour)
11									Zn to equal any value added into C4:C9 + G4:G9 (same font colour)
12									Cu to equal any value added into C4:C9 + G4:G9 (same font colour)
13				Manually Entered Number
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G9:H9	Expression	=$F$9="cu"	text	NO
G9:H9	Expression	=$F$9="zn"	text	NO
G9:H9	Expression	=$F$9="pb"	text	NO
G8:H8	Expression	=$F$8="cu"	text	NO
G8:H8	Expression	=$F$8="zn"	text	NO
G8:H8	Expression	=$F$8="pb"	text	NO
G7:H7	Expression	=$F$7="cu"	text	NO
G7:H7	Expression	=$F$7="zn"	text	NO
G7:H7	Expression	=$F$7="pb"	text	NO
G6:H6	Expression	=$F$6="cu"	text	NO
G6:H6	Expression	=$F$6="zn"	text	NO
G6:H6	Expression	=$F$6="pb"	text	NO
G5:H5	Expression	=$F$5="cu"	text	NO
G5:H5	Expression	=$F$5="zn"	text	NO
G5:H5	Expression	=$F$5="pb"	text	NO
G4:H4,H5:H9	Expression	=$F$4="zn"	text	NO
G4:H4,H5:H9	Expression	=$F$4="pb"	text	NO
G4:H4,H5:H9	Expression	=$F$4="cu"	text	NO
C9	Expression	=$B$9="cu"	text	NO
C9	Expression	=$B$9="zn"	text	NO
C9	Expression	=$B$9="pb"	text	NO
C8	Expression	=$B$8="cu"	text	NO
C8	Expression	=$B$8="zn"	text	NO
C8	Expression	=$B$8="pb"	text	NO
C7	Expression	=$B$7="cu"	text	NO
C7	Expression	=$B$7="zn"	text	NO
C7	Expression	=$B$7="pb"	text	NO
C6	Expression	=$B$6="cu"	text	NO
C6	Expression	=$B$6="zn"	text	NO
C6	Expression	=$B$6="Pb"	text	NO
C5	Expression	=$B$5="Cu"	text	NO
C5	Expression	=$B$5="pb"	text	NO
C5	Expression	=$B$5="zn"	text	NO
C4	Expression	=$B$4="cu"	text	NO
C4	Expression	=$B$4="zn"	text	NO
C4	Expression	=$B$4="Pb"	text	NO
J4:J6,B4:C9,F4:H9	Cell Value	contains "Cu"	text	NO
J4:J6,B4:C9,F4:H9	Cell Value	contains "Zn"	text	NO
J4:J6,B4:C9,F4:H9	Cell Value	contains "Pb"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B4:B9	List	=$L$3:$L$7
F4:F9	List	=$L$3:$L$7

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Monday	02/03/2020	D/S		Monday	02/03/2020	N/S
2
3	Trucks	Product	Loads		Trucks	Product	Loads		24hr	Total Loads		Pb
4	697	Pb	8		697	Zn	1		Pb	12		Zn
5	698	Cu	3		698	Pb	1		Zn	11		Cu
6	699	Pb	2		699	Cu	2		Cu	5		Workshop
7	700	Zn	9		700	Pb	1					Shift Change
8	701	Workshop			701	Zn	1
9	T533	Shift Change			T533	Shift Change
List

Cell Formulas
Range	Formula
J4:J6	J4	=SUMIF($B$4:$B$7,I4,$C$4:$C$7)+SUMIF($F$4:$F$8,I4,$G$4:$G$8)

 

[Tricky7985]

BINGO ! works great. I just used conditional formatting in cells J7/J8/J9 to keep the colors. Thank you very much for your time.
Regards
Richard

 

[Tricky7985]

Hi & welcome to MrExcel.
How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Monday	02/03/2020	D/S		Monday	02/03/2020	N/S
2
3	Trucks	Product	Loads		Trucks	Product	Loads		24hr	Total Loads		Pb
4	697	Pb	8		697	Zn	1		Pb	12		Zn
5	698	Cu	3		698	Pb	1		Zn	11		Cu
6	699	Pb	2		699	Cu	2		Cu	5		Workshop
7	700	Zn	9		700	Pb	1					Shift Change
8	701	Workshop			701	Zn	1
9	T533	Shift Change			T533	Shift Change
List

Cell Formulas
Range	Formula
J4:J6	J4	=SUMIF($B$4:$B$7,I4,$C$4:$C$7)+SUMIF($F$4:$F$8,I4,$G$4:$G$8)

 

[Tricky7985]

Worked great, Thank You.
How can i copy this table (with all formats and formulas) to another table underneath?
Each table represents (24hrs - 1day) i would like to have 7 in total to represent 1 week, on one sheet.
I have tried copy and paste, copy and paste special, it copies it but doesn't let the formulas work.

 

[Fluff]

As that is a different question, it needs a new thread. Thanks