# Sum cells in different rows/columns based on multiple criteria.

#### mboas

##### New Member
Hello mrexcel people!

I have a problem i would love your assistance with.

I have a dataset in range A3:BM448.

This dataset has in range A3:A448 multiple blanks and a few string variables (salary, expense, vacation and others), and in the range A3:BM3 a string of names.

Ideally what i would like to do is use a sum function to sum up values if they match both the string requirement in A3:A448 and the name requirement in A3:BM3. It would add up multiple numbers across various columns and cells.

An example is shown below:
 Name N1 N2 N3 N4 N5 N6 N7 N8 N9 Salary 323 23 23123 Salary 4241 Expense 424 453 Vacation 4241 Vacation Salary 424 445 534 Salary 424

<tbody>
</tbody>

How would i sum up the values corresponding to the string "salary", and the name variable "N1, N2 and N4"?

Any help would be greatly appreciated.

Regards

Mboas

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### JamesLoft

##### New Member
Hello, have you tried using =[FONT=&quot]CONCATENATE(A2," sold ",B2," units.") formula?[/FONT]

#### Eric W

##### MrExcel MVP
Welcome to the forum.

Maybe:

ABCDEFGHIJ
1
2NameN1N2N3N4N5N6N7N8N9
3Salary3232323123
4Salary4241
5
6Expense424453
7Vacation4241
8Vacation
9Salary424445534
10
11Salary424
12
13TypeName(s)Sum
14SalaryN11194
15N2
16N4

</tbody>
Sheet5

Worksheet Formulas
CellFormula
D14=SUMPRODUCT((B3:J11)*(A3:A11=B14)*(ISNUMBER(MATCH(B2:J2,C14:C18,0))))

</tbody>

<tbody>
</tbody>

Change the ranges to match your actual sheet. Let us know if this works for you.

#### mboas

##### New Member
Eric that is amazing!

Works perfectly, so simple, your help is much appreciated!

Happy holidays friend.

best regards

Mboas

#### Eric W

##### MrExcel MVP
Happy to help!

Happy New Year to you!

Replies
9
Views
443
Replies
2
Views
342
Replies
6
Views
180
Replies
1
Views
599
Replies
1
Views
503

1,191,029
Messages
5,984,226
Members
439,878
Latest member
melodysc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back