Sum alternating columns in a single row using if equation?

Agreen07 · Jan 30th 2017

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.

I would appreciate any advice!

[ATTACH=CONFIG]71204[/ATTACH]

**KjBox** · Jan 30th 2017

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:

Code

Sub SumPctByColor()
    Dim x, Colors(), i As Long, ii As Long, lCount As Long
    
    With ActiveSheet.Cells(2, 1).CurrentRegion
        lCount = .Rows(1).SpecialCells(4).Count
        .Offset(1, lCount).Resize(.Rows.Count - 1, .Columns.Count - lCount).Clear
        x = .Value
        For i = lCount + 1 To UBound(x, 2)
            If x(1, i) <> "" Then
                ii = ii + 1
                ReDim Preserve Colors(1 To ii)
                Colors(ii) = x(1, i)
            End If
        Next
        For i = 2 To UBound(x, 1)
            For ii = 2 To lCount Step 2
                iii = Application.Match(x(i, ii), Colors, 0)
                x(i, iii + lCount) = x(i, iii + lCount) + x(i, ii - 1)
            Next
        Next
        .Value = x
        .Offset(1, lCount).Resize(UBound(x, 1) - 1, UBound(Colors)).NumberFormat = "0%"
    End With
    
    
End Sub

Display More

richadj4 · Feb 2nd 2017

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)

Agreen07 · Feb 7th 2017

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

This is so helpful, thanks!

Sum alternating columns in a single row using if equation?

Participate now!

Share