Greetings,
I have a sheet with data that includes Vehicle, Milage, Cost, etc. What I'm
trying to do is find a formula that will allow me to, for example, sum the
cost of the last 4 entries for vehicle 1. Changing the sort order of the
data is, unfortunately, not an option.
There are a number of ways that I can sum all of the entries for vehicle 1,
(assume named ranges for vehicle and cost columns):

Sumif(vehicle,"v1",cost)
Sumproduct(--(vehicle = "v1"),cost)
{Sum(If(vehicle="v1",cost,0))}

I just can't seem to figure out a way to add the criteria of only going to 4
instances. It can be done with a UDF, but I like to avoid that as much as
possible, as they tend to be much slower than built in functions (or, at
least they are when I write them).

Any suggestions that anyone has would be greatly appreciated.
--
Mike Lee
McKinney,TX USA

=SUM(N(OFFSET(cost,LARGE(IF(vehicle="v1",ROW(cost) ),{1,2,3,4})-MIN(ROW(cost)),0)))

it is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Greetings,
I have a sheet with data that includes Vehicle, Milage, Cost, etc. What
I'm
trying to do is find a formula that will allow me to, for example, sum the
cost of the last 4 entries for vehicle 1. Changing the sort order of the
data is, unfortunately, not an option.
There are a number of ways that I can sum all of the entries for vehicle
1,
(assume named ranges for vehicle and cost columns):

Sumif(vehicle,"v1",cost)
Sumproduct(--(vehicle = "v1"),cost)
{Sum(If(vehicle="v1",cost,0))}

I just can't seem to figure out a way to add the criteria of only going to
4
instances. It can be done with a UDF, but I like to avoid that as much as
possible, as they tend to be much slower than built in functions (or, at
least they are when I write them).

Any suggestions that anyone has would be greatly appreciated.
--
Mike Lee
McKinney,TX USA

Bob,
Works like a charm. I still don't know that I understand exactly what it's
doing (I'm not familiar with the "N()" function), but hopefully I can use
help and formula auditing and it'll make more sense.

Thanks again for the help.
--
Mike Lee
McKinney,TX USA


"Bob Phillips" wrote:

=SUM(N(OFFSET(cost,LARGE(IF(vehicle="v1",ROW(cost) ),{1,2,3,4})-MIN(ROW(cost)),0)))

it is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"mikelee101" <mikelee101athotmaildotcom wrote in message
...
Greetings,
I have a sheet with data that includes Vehicle, Milage, Cost, etc. What
I'm
trying to do is find a formula that will allow me to, for example, sum the
cost of the last 4 entries for vehicle 1. Changing the sort order of the
data is, unfortunately, not an option.
There are a number of ways that I can sum all of the entries for vehicle
1,
(assume named ranges for vehicle and cost columns):

Sumif(vehicle,"v1",cost)
Sumproduct(--(vehicle = "v1"),cost)
{Sum(If(vehicle="v1",cost,0))}

I just can't seem to figure out a way to add the criteria of only going to
4
instances. It can be done with a UDF, but I like to avoid that as much as
possible, as they tend to be much slower than built in functions (or, at
least they are when I write them).

Any suggestions that anyone has would be greatly appreciated.
--
Mike Lee
McKinney,TX USA