I have an Excel spreadsheet that contains hundreds of rows. In each row, the first 20 cells (Column A to Column T) contain an alternating pattern: the first cell contains a percentage followed by a color followed by a percentage followed by a number, etc. After Column T, the next 17 columns have Column Titles, each displaying a unique color name.
For all of the instances in which one of the 17 colors appears in the first 20 cells in a single row, I would like to sum the percentage in the cell located just to the left of the color name. Please see the attached example spreadsheet. Specifically, I am trying to determine what equation I can include in cells U2:AK2 and U3:AK3, so I don't have to manually include these in the Excel spreadsheet for hundreds of rows.
I would appreciate any advice!