Problems with INDEX formula
 

I have a range of vehicles splited by mileage (a row with 10 categories) and
in another sheet I have other categories that I have to sum the vehicles of
the nearest higher mileage range. Ex

Model 10.000 20.000 30.000 40.000 50.000 60.000
.........
X 2 3 5 3
8 9
Y 4 2 3 2
7 4
Z 5 7 2 1
6 5

and I need to know the sum vehicles of around and higher 44.000 miles which
in this specific case is inside the 50.000miles range and should return
21(sum of vehicles inside this category)
I can not use if conditions cause I ´m limited to 7 whereas I have 10
categories.
Is there anybody who can help me?

Problems with INDEX formula
 

Assume the table is A1:G4 with the mileage in B1:G1, the Model header in A1,
then model type in A2:A4 and the numerical data in B2:G4

=SUM(OFFSET($A$1,1,MATCH(SMALL(A1:G1,COUNTIF(A1:G1 ,"<"&K1)+1),A1:G1,0)-1,ROWS(A1:G4)-1,))

will return 21, a non volatile version

=SUM(INDEX(A1:G4,2,MATCH(SMALL(A1:G1,COUNTIF(A1:G1 ,"<"&K1)+1),A1:G1,0)):INDEX(A1:G4,ROWS(A1:G4),MATC H(SMALL(A1:G1,COUNTIF(A1:G1,"<"&K1)+1),A1:G1,0)))



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"bc" wrote in message
...
I have a range of vehicles splited by mileage (a row with 10 categories)
and
in another sheet I have other categories that I have to sum the vehicles
of
the nearest higher mileage range. Ex

Model 10.000 20.000 30.000 40.000 50.000
60.000
.........
X 2 3 5 3
8 9
Y 4 2 3 2
7 4
Z 5 7 2 1
6 5

and I need to know the sum vehicles of around and higher 44.000 miles
which
in this specific case is inside the 50.000miles range and should return
21(sum of vehicles inside this category)
I can not use if conditions cause I ´m limited to 7 whereas I have 10
categories.
Is there anybody who can help me?

Problems with INDEX formula
 

Another option that uses a much less complicated formula:

If you had your table in descending order:

Rather than like this:

10....20....30....40....50.....60

Do this:

60....50....40....30....20....10

Then:

=SUM(INDEX(B2:G4,,MATCH(44,B1:G1,-1)))

Biff

"Peo Sjoblom" wrote in message
...
Assume the table is A1:G4 with the mileage in B1:G1, the Model header in
A1, then model type in A2:A4 and the numerical data in B2:G4

=SUM(OFFSET($A$1,1,MATCH(SMALL(A1:G1,COUNTIF(A1:G1 ,"<"&K1)+1),A1:G1,0)-1,ROWS(A1:G4)-1,))

will return 21, a non volatile version

=SUM(INDEX(A1:G4,2,MATCH(SMALL(A1:G1,COUNTIF(A1:G1 ,"<"&K1)+1),A1:G1,0)):INDEX(A1:G4,ROWS(A1:G4),MATC H(SMALL(A1:G1,COUNTIF(A1:G1,"<"&K1)+1),A1:G1,0)))



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"bc" wrote in message
...
I have a range of vehicles splited by mileage (a row with 10 categories)
and
in another sheet I have other categories that I have to sum the vehicles
of
the nearest higher mileage range. Ex

Model 10.000 20.000 30.000 40.000 50.000 60.000
.........
X 2 3 5 3
8 9
Y 4 2 3 2
7 4
Z 5 7 2 1
6 5

and I need to know the sum vehicles of around and higher 44.000 miles
which
in this specific case is inside the 50.000miles range and should return
21(sum of vehicles inside this category)
I can not use if conditions cause I ´m limited to 7 whereas I have 10
categories.
Is there anybody who can help me?

Problems with INDEX formula
 

fwliw .. I've just plonked another crack in your earlier post
(missed your repeat posting here, ugh ...)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---