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

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.

## closed as unclear what you're asking by Dave, DavidPostill♦, fixer1234, Simon Sheehan, MosesJun 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.

## 2 Answers

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.

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 