Find all instances of a name, then return sum of values

[HelloAce]

Hi,

I have a workbook with thousands of unique names in column 'J'.
The other columns also contain these same names, often repeated many times over, with a different number value alongside each name.
The numbers are one space to the left of the names.

For instance John Jones may have a value 24 next to his name in S7 (which would be in R7), a 11 value next to his name in AB12, and a 35 value next to his name in ZZ55.
I am looking for a formula to find and add up the numbers that correspond to each name from column J. I would like the formula alongside the names in column 'J' that finds each of these instances, and then returns, in this instance, a value of 70 next to John Jones' name in column 'J'.

Is this possible?
Thanks

 

[Oscar Cronquist]

Book1
	A	B	C	D	E	F
1	26	John	93	Lisa	96	Robert
2	59	Jennifer	98	Robert	37	Jennifer
3	29	Laura	12	Sandra	36	John
4	50	Robert	89	Helen	3	Lisa
5	2	Michael	33	Jennifer	32	Michael
6
7		Name:	Michael
8		Numbers:	2
9			32
Sheet1

Array formula in cell C8:
=SMALL(IF($C$7=$B$1:$F$5,$A$1:$E$5,""),ROWS($A$1:A1))

 

[Ingolf]

Hi,

use SUMIFS, in say K2 =SUMIFS(R2:R500,S2:S500,J2) and same for all columns
R2:R500 is range with numbers (adjust the range)
S2:S500 is range with names (adjust the range)

Column J is with unique names

 

[HelloAce]

Hi, thank you for your response!
I was hoping the formula would add up all the numerical totals for each instance of each name (outside of column 'J'). In the example you have given here, the formula I'm hoping for would return a value of 34 for Michael. Then the next formula would return a value of 244 for Robert, etc. So essentially it is finding each instance of the name (eg Michael) and finding its offset value (one cell to the left of each instance of the name), then adding the totals together.
The formula would be put alongside each name listed in my 'J' column so I can quickly see the totals for each name throughout the entire workbook.
Thanks

 

[HelloAce]

	A	B	C	D	E	F
1	26	John	93	Lisa	96	Robert
2	59	Jennifer	98	Robert	37	Jennifer
3	29	Laura	12	Sandra	36	John
4	50	Robert	89	Helen	3	Lisa
5	2	Michael	33	Jennifer	32	Michael
6
7		Name:	Michael
8		Numbers:	2
9			32

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

Sheet1​

Array formula in cell C8:
=SMALL(IF($C$7=$B$1:$F$5,$A$1:$E$5,""),ROWS($A$1:A1))

I was hoping the formula would add up all the numerical totals for each instance of each name (outside of column 'J'). In the example you have given here, the formula I'm hoping for would return a value of 34 for Michael. Then the next formula would return a value of 244 for Robert, etc. So essentially it is finding each instance of the name (eg Michael) and finding its offset value (one cell to the left of each instance of the name), then adding the totals together.
The formula would be put alongside each name listed in my 'J' column so I can quickly see the totals for each name throughout the entire workbook.
Thanks

 

[HelloAce]

Hi,

use SUMIFS, in say K2 =SUMIFS(R2:R500,S2:S500,J2) and same for all columns
R2:R500 is range with numbers (adjust the range)
S2:S500 is range with names (adjust the range)

Column J is with unique names

Hi, thank you for your response!

I have the names spread out over many different columns, how can I modify this to cover the entire workbook? Is that possible?
I tried adjusting both ranges with the entire workbook range (L2:YU170) but it returned a result of 0.

Thanks

 

[Oscar Cronquist]

=SUMIFS(R2:R500,S2:S500,J2)

Look at the cell references, they don't match.

Your formula should be:
=SUMIFS(L2:YU170,M2:YV170,J2)

or possibly

=SUMIFS(K2:YT170, L2:YU170)

Hi, thank you for your response!

I have the names spread out over many different columns, how can I modify this to cover the entire workbook? Is that possible?
I tried adjusting both ranges with the entire workbook range (L2:YU170) but it returned a result of 0.

Thanks

 

[HelloAce]

=SUMIFS(R2:R500,S2:S500,J2)

Look at the cell references, they don't match.

Your formula should be:
=SUMIFS(L2:YU170,M2:YV170,J2)

or possibly

=SUMIFS(K2:YT170, L2:YU170)

Awesome! Thanks so much for your help![FONT=&quot]
[/FONT]