# Sum alternating columns in a single row using if equation?

• Hello,

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.

[ATTACH=CONFIG]71204[/ATTACH]

## Files

• Re: Sum alternating columns in a single row using if equation?

I don't think you can achieve what you want by formula (though I stand to be corrected!).

Here is a VBA solution, click the button to populate cells in columns U to AK for all the rows of data.

Code assigned to the button:

## Files

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: Sum alternating columns in a single row using if equation?

This formula in cell U2 can be copied across and down to give the correct results:
=SUMIF(\$B2:\$T2,U\$1,\$A2:\$S2)

• Re: Sum alternating columns in a single row using if equation?