Lookup partial text and sum data based multiple cells

[Rachel22]

Hi All,

I am trying to find the following. I have a set of data for call duration for sales people.

1. I need to use a lookup formula to calculate the total duration of call time per sales person in one cell, pulling partial data of the person's name from Column B AND C.

2. Add a rule to this that if the number in the FROM column starts with +1 and add up the durations for these calls only.

The entire range is A1:E722

I trust someone can guide me on the best way to achieve these results?

Thank you

 

[DanteAmor]

Hi and welcome to MrExcel!

I'm not sure if the following formula is what you need, since in your image apparently no record satisfies the rules.

Dante Amor
	A	B	C	D	E	F	G	H
1	From	To	Forward	Result	Duration		Person	Sum
2	+1 618-853- Evan		Luvo Dunjwa		00:06:47		Darryn	00:00:00
3	07 - UK		Luvo Dunjwa		00:02:47		Luvo	00:22:53
4	07 - UK		Tristan Fautley		00:03:15		Mick	00:11:07
5	David cowlin - +1 317-650	Luvo Dunjwa			00:16:06
6	+1 618-853- Evan		Mick Uk ext 800		00:11:07
7
Hoja22

Cell Formulas
Range	Formula
H2:H4	H2	=SUM(IF(ISNUMBER(SEARCH("+1",$A$2:$A$722)),IF(ISNUMBER(SEARCH(G2,$B$2:$C$722)),$E$2:$E$722)))
Press CTRL+SHIFT+ENTER to enter array formulas.

----------------
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
----------------

 

[Peter_SSs]

I agree with Dante's comments about XL2BB. Please consider for future use.

Are these the results that you are looking for? (For use with 365 and 2021)

22 08 04.xlsm
	A	B	C	D	E	F	G	H	I
1	From	To	Forward	Result	Duration		Person	Sum (All)	Sum (+1)
2	+1 618-853- Evan		Luvo Dunjwa		00:06:47		Darryn	00:00:00	00:00:00
3	07 - UK		Luvo Dunjwa		00:02:47		Luvo	00:25:40	00:22:53
4	07 - UK		Tristan Fautley		00:03:15		Mick	00:11:07	00:11:07
5	David cowlin - +1 317-650	Luvo Dunjwa			00:16:06
6	+1 618-853- Evan		Mick Uk ext 800		00:11:07
Call Times

Cell Formulas
Range	Formula
H2:H4	H2	=SUM(FILTER(E$2:E$722,ISNUMBER(SEARCH(G2,B$2:B$722&C$2:C$722)),0))
I2:I4	I2	=SUM(FILTER(E$2:E$722,ISNUMBER(SEARCH(G2,B$2:B$722&C$2:C$722))*ISNUMBER(FIND("+1",A$2:A$722)),0))

 

[Peter_SSs]

Are these the results that you are looking for? (For use with 365 and 2021)

If you need to include older versions then I think these with standard-entry should also be an option.

22 08 04.xlsm
	A	B	C	D	E	F	G	H	I
1	From	To	Forward	Result	Duration		Person	Sum (All)	Sum (+1)
2	+1 618-853- Evan		Luvo Dunjwa		00:06:47		Darryn	00:00:00	00:00:00
3	07 - UK		Luvo Dunjwa		00:02:47		Luvo	00:25:40	00:22:53
4	07 - UK		Tristan Fautley		00:03:15		Mick	00:11:07	00:11:07
5	David cowlin - +1 317-650	Luvo Dunjwa			00:16:06
6	+1 618-853- Evan		Mick Uk ext 800		00:11:07
Call Times (2)

Cell Formulas
Range	Formula
H2:H4	H2	=SUMPRODUCT(ISNUMBER(SEARCH(G2,B$2:B$722))+ISNUMBER(SEARCH(G2,C$2:C$722)),E$2:E$722)
I2:I4	I2	=SUMPRODUCT(ISNUMBER(SEARCH(G2,B$2:B$722))+ISNUMBER(SEARCH(G2,C$2:C$722))*ISNUMBER(FIND("+1",A$2:A$722)),E$2:E$722)

 

[Phuoc]

Or try

Book1
	A	B	C	D	E	F	G	H	I
1	From	To	Forward	Result	Duration		Person	Sum (All)	Sum (+1)
2	+1 618-853- Evan		Luvo Dunjwa		0:06:47		Darryn	0:00:00	0:00:00
3	07 - UK		Luvo Dunjwa		0:02:47		Luvo	0:25:40	0:06:47
4	07 - UK		Tristan Fautley		0:03:15		Mick	0:11:07	0:11:07
5	David cowlin - +1 317-650	Luvo Dunjwa			0:16:06
6	+1 618-853- Evan		Mick Uk ext 800		0:11:07
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=SUMPRODUCT(--ISNUMBER(SEARCH(G2,B$2:B$722&"|"&C$2:C$722)),E$2:E$722)
I2:I4	I2	=SUMPRODUCT(ISNUMBER(SEARCH(G2,B$2:B$722&"|"&C$2:C$722))*(LEFT(A$2:A$722,2)="+1"),E$2:E$722)