collect sum if cell contain specific text

[tonyrensya]

Hello,

Here's my question.
I want collect sum of the cell besides the cell if cell contain specific text (Name)
target formula in A2 to D2

Thank you!

 

[Trebor76]

Hi Tony,

Try this formula (there's other ways I'm sure) in cell A2...

=SUMIF($A$4:$A$8,"*"&A1&"*",$B$4:$B$8)+SUMIF($C$4:$C$8,"*"&A1&"*",$D$4:$D$8)

...and copy it across to B2:D2

Note Jack's count is 10 not seven as per your screen shot.

Regards,

Robert

 

[tonyrensya]

Thank you Robert!
I have way more columns and rows in the actual table, the formula will be too long, maybe there's another way?

 

[Trebor76]

No I don't know of another way but someone in the forum will I'm sure

 

[FormR]

Hi, here's a couple of options, if you can have names that are subsets of other names then the SUMPRODUCT() option is more robust (as demonstrated).

tempp.xlsx
	A	B	C	D	E	F
1	Tom	Mary	Jack	Jan	Jackson
2	22	21	22	7	5
3	22	21	17	7	5
4
5	Tom Mary	3	Mary Jack	2	Jackson	5
6	Jack Jan	3	Tom Jack	2	Mary	4
7	Tom Jack	3	Mary Jan	2	Mary	5
8	Tom	3	Jan	2	Tom	4
9	Mary	3	Mary	2	Tom Jack	7
Sheet1

Cell Formulas
Range	Formula
A2:E2	A2	=SUMIFS($B$5:$F$9,$A$5:$E$9,"*"&A1&"*")
A3:E3	A3	=SUMPRODUCT($B$5:$F$9,--ISNUMBER(SEARCH(" "&A1&" "," "&$A$5:$E$9&" ")))

 

[tonyrensya]

Hi, here's a couple of options, if you can have names that are subsets of other names then the SUMPRODUCT() option is more robust (as demonstrated).

tempp.xlsx
	A	B	C	D	E	F
1	Tom	Mary	Jack	Jan	Jackson
2	22	21	22	7	5
3	22	21	17	7	5
4
5	Tom Mary	3	Mary Jack	2	Jackson	5
6	Jack Jan	3	Tom Jack	2	Mary	4
7	Tom Jack	3	Mary Jan	2	Mary	5
8	Tom	3	Jan	2	Tom	4
9	Mary	3	Mary	2	Tom Jack	7
Sheet1

Cell Formulas
Range	Formula
A2:E2	A2	=SUMIFS($B$5:$F$9,$A$5:$E$9,"*"&A1&"*")
A3:E3	A3	=SUMPRODUCT($B$5:$F$9,--ISNUMBER(SEARCH(" "&A1&" "," "&$A$5:$E$9&" ")))

Thank you FormR!
It works great for me.