# Variable Ranges

#### BCS2

##### Board Regular
I have a sheet that is sorted alphabetically by Salesperson. I am calculating this data as a whole but also want to break it down to get some data based on the salesperson.

What I need is a formula that will identify a range based on the name of the salesperson and only do calculations within that range. Sample data below.
Experiment.xls
NOPQ
3RequestedTurnTimeActualTurnTime
4Amburgey24:2818:13
5Amburgey30:407:35
6Amburgey24:2017:00
7Amburgey24:2018:00
8Anliker21:214:26
9Anliker25:4619:06
10Anliker6:400:15
11Anliker0:100:10
1-26 to 1-30

What I am trying to get is the average requested turn time, average actual turn time and # of on-times for each salesperson.

Keep in mind the ranges for each salesperson will change by week. They might have 5 requests this week and 10 the next, but they will always be together in the worksheet because they are sorted.

Any suggestions?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### parry

##### MrExcel MVP
Hi, you could use the Subtotal formula (eg =SUBTOTAL(1,O2:O11) and then just use an autofilter to filter the name you want to see.

#### Zack Barresse

##### MrExcel MVP
Given that the sample data is N3:P11 on sheet 1-26 to 1-30, sorted on the SalesPerson column:

Summary sheet...
Book4
ABCDEF
1StartEndSalespersonAvgRTTAvgATT#On-Time
214Amburgey1.081250.6333334
358Anliker0.5619790.2494794
4
Summary

Formulas...

A2:

=MATCH(\$C2,'1-26 to 1-30 '!\$N\$4:\$N\$11,0)

B2:

=MATCH(\$C2,'1-26 to 1-30 '!\$N\$4:\$N\$11)

D2, copied to E2, then down:

=AVERAGE(INDEX('1-26 to 1-30 '!O\$4:O\$11,\$A2):INDEX('1-26 to 1-30 '!O\$4:O\$11,\$B2))

F2:

=SUMPRODUCT(--(OFFSET('1-26 to 1-30 '!\$O\$4,A2-1,0,B2-A2+1,1)>=OFFSET('1-26 to 1-30 '!\$P\$4,A2-1,0,B2-A2+1,1)))

#### BCS2

##### Board Regular

Thanks for the code!

I have them all working accept for the SUMPRODUCT formula. I got kind of lost when trying to logic my way through it. I am not familiar with the OFFSET function. Can you explain what's going on here? I looked at the help file in Excel but didn't get much out of it.

I have substituted the corresponding cell values in the formula that relate to my sheet and I get a #VALUE! error.

Thanks

##### MrExcel MVP
BCS2 said:
...I have substituted the corresponding cell values in the formula that relate to my sheet and I get a #VALUE! error.
...

Everywhere or just in a few cells?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,953
Messages
5,856,481
Members
431,817
Latest member
db74

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back