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

[mboas]

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

 

[JamesLoft]

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

 

[Eric W]

Welcome to the forum.

Maybe:

	A	B	C	D	E	F	G	H	I	J
1
2	Name	N1	N2	N3	N4	N5	N6	N7	N8	N9
3	Salary	323	23			23123
4	Salary			4241
5
6	Expense					424		453
7	Vacation				4241
8	Vacation
9	Salary				424	445				534
10
11	Salary				424
12
13		Type	Name(s)	Sum
14		Salary	N1	1194
15			N2
16			N4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula D14 =SUMPRODUCT((B3:J11)*(A3:A11=B14)*(ISNUMBER(MATCH(B2:J2,C14:C18,0)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

 

[mboas]

Eric that is amazing!

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

Happy holidays friend.

best regards

Mboas

 

[Eric W]

Happy to help!

Happy New Year to you!