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

sample table

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.

closed as unclear what you're asking by Dave, DavidPostill, fixer1234, Simon Sheehan, Moses Jun 23 '16 at 18:51

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.


Formula for cell B2


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.


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
startRow = startRow + 1
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


enter image description here

After running VBa

enter image description here

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)

enter image description here

And run the exact same VBa

enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.