# 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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### 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?

Replies
0
Views
446
Replies
1
Views
229
Replies
5
Views
361
Replies
19
Views
847
Replies
1
Views
476

1,172,050
Messages
5,878,908
Members
433,383
Latest member
swisshome

### 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