Sum of Cells By Font Color

Tricky7985

New Member
Joined
Mar 26, 2020
Messages
4
Office Version
2016
Platform
Windows
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.o_O
Truck Forecast - Copy.xlsm
ABCDEFGHIJKLMN
1Monday2/03/2020D/SMonday2/03/2020N/S
2
3TrucksProductLoadsTrucksProductLoads24hrTotal LoadsPb
4697Pb8697Zn1PbZn
5698Cu3698Pb1ZnCu
6699Pb2699Cu2CuWorkshop
7700Zn9700Pb1Shift Change
8701Workshop701Zn1
9T533Shift ChangeT533Shift 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)
13Manually Entered Number
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G9:H9Expression=$F$9="cu"textNO
G9:H9Expression=$F$9="zn"textNO
G9:H9Expression=$F$9="pb"textNO
G8:H8Expression=$F$8="cu"textNO
G8:H8Expression=$F$8="zn"textNO
G8:H8Expression=$F$8="pb"textNO
G7:H7Expression=$F$7="cu"textNO
G7:H7Expression=$F$7="zn"textNO
G7:H7Expression=$F$7="pb"textNO
G6:H6Expression=$F$6="cu"textNO
G6:H6Expression=$F$6="zn"textNO
G6:H6Expression=$F$6="pb"textNO
G5:H5Expression=$F$5="cu"textNO
G5:H5Expression=$F$5="zn"textNO
G5:H5Expression=$F$5="pb"textNO
G4:H4,H5:H9Expression=$F$4="zn"textNO
G4:H4,H5:H9Expression=$F$4="pb"textNO
G4:H4,H5:H9Expression=$F$4="cu"textNO
C9Expression=$B$9="cu"textNO
C9Expression=$B$9="zn"textNO
C9Expression=$B$9="pb"textNO
C8Expression=$B$8="cu"textNO
C8Expression=$B$8="zn"textNO
C8Expression=$B$8="pb"textNO
C7Expression=$B$7="cu"textNO
C7Expression=$B$7="zn"textNO
C7Expression=$B$7="pb"textNO
C6Expression=$B$6="cu"textNO
C6Expression=$B$6="zn"textNO
C6Expression=$B$6="Pb"textNO
C5Expression=$B$5="Cu"textNO
C5Expression=$B$5="pb"textNO
C5Expression=$B$5="zn"textNO
C4Expression=$B$4="cu"textNO
C4Expression=$B$4="zn"textNO
C4Expression=$B$4="Pb"textNO
J4:J6,B4:C9,F4:H9Cell Valuecontains "Cu"textNO
J4:J6,B4:C9,F4:H9Cell Valuecontains "Zn"textNO
J4:J6,B4:C9,F4:H9Cell Valuecontains "Pb"textNO
Cells with Data Validation
CellAllowCriteria
B4:B9List=$L$3:$L$7
F4:F9List=$L$3:$L$7
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
+Fluff.xlsm
ABCDEFGHIJKL
1Monday02/03/2020D/SMonday02/03/2020N/S
2
3TrucksProductLoadsTrucksProductLoads24hrTotal LoadsPb
4697Pb8697Zn1Pb12Zn
5698Cu3698Pb1Zn11Cu
6699Pb2699Cu2Cu5Workshop
7700Zn9700Pb1Shift Change
8701Workshop701Zn1
9T533Shift ChangeT533Shift Change
List
Cell Formulas
RangeFormula
J4:J6J4=SUMIF($B$4:$B$7,I4,$C$4:$C$7)+SUMIF($F$4:$F$8,I4,$G$4:$G$8)
 

Tricky7985

New Member
Joined
Mar 26, 2020
Messages
4
Office Version
2016
Platform
Windows
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

New Member
Joined
Mar 26, 2020
Messages
4
Office Version
2016
Platform
Windows
Hi & welcome to MrExcel.
How about
+Fluff.xlsm
ABCDEFGHIJKL
1Monday02/03/2020D/SMonday02/03/2020N/S
2
3TrucksProductLoadsTrucksProductLoads24hrTotal LoadsPb
4697Pb8697Zn1Pb12Zn
5698Cu3698Pb1Zn11Cu
6699Pb2699Cu2Cu5Workshop
7700Zn9700Pb1Shift Change
8701Workshop701Zn1
9T533Shift ChangeT533Shift Change
List
Cell Formulas
RangeFormula
J4:J6J4=SUMIF($B$4:$B$7,I4,$C$4:$C$7)+SUMIF($F$4:$F$8,I4,$G$4:$G$8)
 

Tricky7985

New Member
Joined
Mar 26, 2020
Messages
4
Office Version
2016
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
As that is a different question, it needs a new thread. Thanks
 

Forum statistics

Threads
1,089,220
Messages
5,406,928
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top