Excel: sum values in multiple columns within each row if criteria in another column (also multiple) are met

Question

Please refer to the image attached containing a sample table that is similar to the actual workbook:

I want to write formulas in columns B-D such that for each customer,ratings are added based on type. Example, for customer Ava, I want the formulas to produce this: 22 under column B (Type A) since she has all Type As in her row with ratings 7, 8 and 7 (7 plus 8 plus 7 equals 22). If a type is not found in the row (in this example Type B and C), the results in columns C and D (Type B and C, respectively) is 0. The "Type, Name, Code, and Ratings" columns will extend to the right (to cover monthly) and have other columns in between them.

I've tried nested ifs, vlookups, sumifs, index match, etc but I haven't found the right formula mix for them to be practicable in the long run since new groups of columns for the "Type, Name, Code, and Ratings" are added constantly. The formulas I created needed to be updated every time new groups of these columns are added.

score 3 · Answer 1 · edited Jun 07 '16 at 13:51

3

Formula for cell B2

=SUM((($E2:$XFA2)=B$1)*IFERROR(VALUE($H2:$XFD2),0))

This is really the "matrix formula" (need to be confirmed by CTRL+SHIFT+ENTER) After confirmation stretch this cell past columns B, C, D.

This formula finds column "rating" always 3 columns right from "Type" column.

edited Jun 07 '16 at 13:51

Dave

25,513

answered Jun 07 '16 at 13:10

Lluser

905

score 1 · Answer 2 · edited Mar 20 '17 at 10:17

This does what you want, and scales! it's in VBa though. I know you asked for worksheet-function but as you don't (didn't) have a suitable answer, I'm offering this.

Remember, there is no undo so make sure you take a back up of your file first.

Option Explicit    
Sub WalkThePlank()

Dim startRow As Integer
startRow = 2                     'update this if needed Cap'ain, assumes content starts on row 2 as your "headings" be on row 1. Argggghhhh

Dim startCol As Integer
startCol = 69                   '69 is for Column E, the first column you want to look at. If this be wrong, then I'll feed myself to the sharks

Dim currentCol As Integer    
Dim typeToUse As String        
Do While Range("A" & startRow).Value <> ""
currentCol = startCol    
Do While Range(Chr(currentCol) & 1).Value <> ""
    Dim heading As String
    heading = Range(Chr(currentCol) & 1).Value        
    If (LCase(heading) = "type") Then
        typeToUse = Range(Chr(currentCol) & startRow).Value
    End If        
    If (LCase(heading) = "ranking") Then
        If LCase(typeToUse) = "a" Then
            Range("B" & startRow).Value = Range("B" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
        If LCase(typeToUse) = "b" Then
            Range("C" & startRow).Value = Range("C" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
        If LCase(typeToUse) = "c" Then
            Range("D" & startRow).Value = Range("D" & startRow).Value + Range(Chr(currentCol) & startRow).Value
        End If
    End If
    currentCol = currentCol + 1
Loop    
startRow = startRow + 1
Loop    
End Sub

How do I add VBA in MS Office?

The only criteria is, from left to right, the Type column must come before the Ranking.

In your screen shot, you have "groups" of 4 columns

Type Name Code Rating

With the above, you could have a group of 2, or 20! It will still work

If you append an additional group of columns to the far right of your data, be it a group of 2, 3, 4 (as you have ) or any other number, it will still work

Before

After running VBa

Then delete the values in TypeA, TypeB and TypeC column and add 2 more columns (I omitted the column called Argh to show how the different structure doesn't matter)

And run the exact same VBa

Excel: sum values in multiple columns within each row if criteria in another column (also multiple) are met

2 Answers2