Formula Assistance

[jehbro]

I have a spreadsheet where I am trying to extract some information from raw data.

In Column F there is a list of telephone numbers and in Column D there is a number which equates to duration in minutes.

I have to break down all of the data into different areas. So I currently have many lookups similar to =SUMPRODUCT(--(LEFT($F:$F,4)="1281")) This will find all of the telephone numbers that begin with 1281 in Column F. For example, on one sheet the total instances of 1281 is 19.

What I need to be able to do is lookup those 19 instances but then also lookup column D and sum the total duration in minutes for all 19 instances of 1281 only.

Any ideas on a formula I would use to do this?

Thanks in advance!

 

[Aladin Akyurek]

I have a spreadsheet where I am trying to extract some information from raw data.

In Column F there is a list of telephone numbers and in Column D there is a number which equates to duration in minutes.

I have to break down all of the data into different areas. So I currently have many lookups similar to =SUMPRODUCT(--(LEFT($F:$F,4)="1281")) This will find all of the telephone numbers that begin with 1281 in Column F. For example, on one sheet the total instances of 1281 is 19.

What I need to be able to do is lookup those 19 instances but then also lookup column D and sum the total duration in minutes for all 19 instances of 1281 only.

Any ideas on a formula I would use to do this?

Thanks in advance!

Try something like...

=SUMPRODUCT(--(LEFT($F:$F,4)="1281"),$D:$D)

 

[T. Valko]

I have a spreadsheet where I am trying to extract some information from raw data.

In Column F there is a list of telephone numbers and in Column D there is a number which equates to duration in minutes.

I have to break down all of the data into different areas. So I currently have many lookups similar to =SUMPRODUCT(--(LEFT($F:$F,4)="1281")) This will find all of the telephone numbers that begin with 1281 in Column F. For example, on one sheet the total instances of 1281 is 19.

What I need to be able to do is lookup those 19 instances but then also lookup column D and sum the total duration in minutes for all 19 instances of 1281 only.

Any ideas on a formula I would use to do this?

Thanks in advance!

If the phone numbers are TEXT strings...

Book1
	D	E	F
2	24	_	1281-100-2011
3	60	_	1000-112-1234
4	78	_	1281-222-3265
5	57	_	2314-128-1281
6	23	_	1281-000-0001
Sheet1

=SUMIF(F:F,"1281*",D:D)

Or, if you use a cell to hold the criteria:

A2 = 1281

=SUMIF(F:F,A2&"*",D:D)