Best way to calculate?

[Dawn1231]

I have spent hours trying to figure out the best way to capture some data. This is done on a daily basis.
Need to track for several different employees specific PTO, including vacation, sick, and floating time off.

Then quarterly, I need to take the summary and subtract them. Here's where my coworker has started. She is typing in to the fields 8 V for 8 hours vacation time, 2 S for sick, etc. She has then done a conditional format where it will change the color of the cell dependent on the type of PTO it is.

I don't know the best way to capture this. And I can't get the SumByColor VBA I found online to work, so that's another issue altogether.

I don't want to get stuck overthinking something that could be handled in a much easier way. Suggestions?

I am including sample data below in a mini table.

Thank you in advance

Sample from Dawn.xlsm
	A	B	C	D	E	F	G	H	I
4	Last Name	1/1	1/8	1/15	1/22	1/29	2/5	2/12	2/19
5	Alfred	8 V	2 S	8 V	8 V	8 FL		2 S	3 FL
6	Bonnie	8 S
7	Carol					3 FL
8	Donnie	6 FL		8 S		3 V		6 V	6V
9	Elena	2 S
10	Fred	2 S
11	Gary	8 V
Option 3 - Pivot

Cell Formulas
Range	Formula
C4:I4	C4	=B4+7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:I11	Cell Value	contains "FL"	text	NO
C6:I11	Cell Value	contains "S"	text	NO
C6:I11	Cell Value	contains "V"	text	NO
B5:B11,C5:F5,H5:I5	Cell Value	contains "FL"	text	NO
B5:B11,C5:F5,H5:I5	Cell Value	contains "S"	text	NO
B5:B11,C5:F5,H5:I5	Cell Value	contains "V"	text	NO

 

[Dawn1231]

So for this, Row 5 for Alfred would be 24 hours vacation time, 4 sick, and 11 floating.

 

[alansidman]

With Power Query, Unpivot the data and then Group By Function.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Last Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", Int64.Type}, {"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute", "Date"}, {"Value.2", "Type PTO"}, {"Value.1", "PTO Value"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Last Name", "Type PTO"}, {{"PTO Summary", each List.Sum([PTO Value]), type nullable number}})
in
    #"Grouped Rows"

Book2
	A	B	C	D	E	F	G	H	I
4	Last Name	1/1/2022	1/8/2022	1/15/2022	1/22/2022	1/29/2022	2/5/2022	2/12/2022	2/19/2022
5	Alfred	8 V	2 S	8 V	8 V	8 FL		2 S	3 FL
6	Bonnie	8 S
7	Carol					3 FL
8	Donnie	6 FL		8 S		3 V		6 V	6 V
9	Elena	2 S
10	Fred	2 S
11	Gary	8 V
12
13	Last Name	Type PTO	PTO Summary
14	Alfred	V	24
15	Alfred	S	4
16	Alfred	FL	11
17	Bonnie	S	8
18	Carol	FL	3
19	Donnie	FL	6
20	Donnie	S	8
21	Donnie	V	15
22	Elena	S	2
23	Fred	S	2
24	Gary	V	8
Sheet1