Formula for list summary

Ron Rosenfeld

2008-02-13 13:54:30 UTC

Post by Sinner
I have a list below with min & max values. I want a summary in such a
way that the formula calculates the first number in range & last
number and continue for any skiped series.
List
12220
12221
12222
12223
12224
12225
12226
12227
133356
133357
133359
133360
133361
133362
133363
133364
________________________
Start End Quantity
12220 12227 8
133356 133357 2
133359 133364 6

Assumptions:

List is a NAME'd range

D1: Start
E1: End
F1: Quantity

D2: =List
*E2: =INDEX(List,MATCH(FALSE,OFFSET(List,0,0)+1=OFFSET(List,1,0),0))
F2: =COUNTIF(List,">="&D2)-COUNTIF(List,">"&E2)
*D3: =INDEX(List,MATCH(E2,List,0)+1)

*E3:
=INDEX(OFFSET(List,MATCH(D3,List,0)-1,0),MATCH(FALSE,OFFSET(
List,MATCH(D3,List,0)-1,0)+1=OFFSET(List,MATCH(D3,List,0),0),0))

F3: =COUNTIF(List,">="&D3)-COUNTIF(List,">"&E3)

* -- ARRAY ENTERED FORMULAS (Confirm with <ctrl><shift><enter>. Excel will
place braces {...} around the formulas)

Then select D3:F3 and fill down as far as required.

You will get errors when you have filled down too far. You can test for an
error in various ways to suppress the output, if necessary.
--ron