COUNT IF but not if 'X' IS NULL

Hi,

I have a list of vehicle types and a list of MPG figures. I want to do an
average MPG for each vehicle type so I need to know not only how many of the
vehicle type there are but also how many of that type of vehicle have an MPG
figure with them.

E.g. I have 132 Peugeot vehicles, if I total the MPG figures and divide by
132 that gives me the average but only if every one of the Peugeots has a
figure for that month which they dont. For this reason I need to know how
many of those 132 have a figure with them. As the Peugeots are in a list with
other vehicle types I first need to identify Peugeot then identify that MPG
is not null, then count those cells.

You can use an array-entered AVERAGE(IF(... formula to do this - give
details of the columns used for the vehicle name and for the mpg and I
can give you the rest of it.

Pete

On Aug 1, 12:33*pm, Pyrite wrote:
Hi,

I have a list of vehicle types and a list of MPG figures. I want to do an
average MPG for each vehicle type so I need to know not only how many of the
vehicle type there are but also how many of that type of vehicle have an MPG
figure with them.

E.g. I have 132 Peugeot vehicles, if I total the MPG figures and divide by
132 that gives me the average but only if every one of the Peugeots has a
figure for that month which they dont. For this reason I need to know how
many of those 132 have a figure with them. As the Peugeots are in a list with
other vehicle types I first need to identify Peugeot then identify that MPG
is not null, then count those cells.

The vehicle type coloumn is labelled 'Vehicle Type' and is from F13 to F500.
MPG column is 'MPG' and is G13 to G500. Thanks for your help

"Pete_UK" wrote:

You can use an array-entered AVERAGE(IF(... formula to do this - give
details of the columns used for the vehicle name and for the mpg and I
can give you the rest of it.

Pete

On Aug 1, 12:33 pm, Pyrite wrote:
Hi,

I have a list of vehicle types and a list of MPG figures. I want to do an
average MPG for each vehicle type so I need to know not only how many of the
vehicle type there are but also how many of that type of vehicle have an MPG
figure with them.

E.g. I have 132 Peugeot vehicles, if I total the MPG figures and divide by
132 that gives me the average but only if every one of the Peugeots has a
figure for that month which they dont. For this reason I need to know how
many of those 132 have a figure with them. As the Peugeots are in a list with
other vehicle types I first need to identify Peugeot then identify that MPG
is not null, then count those cells.

Okay, well let's say then that you have a list of vehicle types
somewhere else, eg starting in X1 with "Peugeot". Put this array*
formula in Y1

=AVERAGE(IF((F13:F500=X1)*(G13:G500""),G13:G500) )

* As this is an array formula, then once you have typed it in you need
to commit it using Ctrl-Shift-Enter (CSE) instead of the usual
Enter. If you do this correctly then Excel will wrap curly braces
{ } around the formula when viewed in the formula bar - you must not
type these yourself. If you need to amend/edit the formula you must
use CSE again.

If you have other vehicle types in cells below X1, then copy the
formula down column Y as appropriate.

If you want to know how many vehicles, the formula is very similar -
just change AVERAGE to COUNT, and use CSE again.

Hope this helps.

Pete

On Aug 1, 1:05*pm, Pyrite wrote:
The vehicle type coloumn is labelled 'Vehicle Type' and is from F13 to F500.

That works splendidly thank you. I'm not sure how it works but a couple of
re-reads of the formula and I will figure it out and add it to my repatoire
for future use.

Thanks again

"Pete_UK" wrote:

Okay, well let's say then that you have a list of vehicle types
somewhere else, eg starting in X1 with "Peugeot". Put this array*
formula in Y1

=AVERAGE(IF((F13:F500=X1)*(G13:G500""),G13:G500) )

* As this is an array formula, then once you have typed it in you need
to commit it using Ctrl-Shift-Enter (CSE) instead of the usual
Enter. If you do this correctly then Excel will wrap curly braces
{ } around the formula when viewed in the formula bar - you must not
type these yourself. If you need to amend/edit the formula you must
use CSE again.

If you have other vehicle types in cells below X1, then copy the
formula down column Y as appropriate.

If you want to know how many vehicles, the formula is very similar -
just change AVERAGE to COUNT, and use CSE again.

Hope this helps.

Pete

On Aug 1, 1:05 pm, Pyrite wrote:
The vehicle type coloumn is labelled 'Vehicle Type' and is from F13 to F500.
MPG column is 'MPG' and is G13 to G500. Thanks for your help



"Pete_UK" wrote:
You can use an array-entered AVERAGE(IF(... formula to do this - give
details of the columns used for the vehicle name and for the mpg and I
can give you the rest of it.

Pete

On Aug 1, 12:33 pm, Pyrite wrote:
Hi,

I have a list of vehicle types and a list of MPG figures. I want to do an
average MPG for each vehicle type so I need to know not only how many of the
vehicle type there are but also how many of that type of vehicle have an MPG
figure with them.

E.g. I have 132 Peugeot vehicles, if I total the MPG figures and divide by
132 that gives me the average but only if every one of the Peugeots has a
figure for that month which they dont. For this reason I need to know how
many of those 132 have a figure with them. As the Peugeots are in a list with
other vehicle types I first need to identify Peugeot then identify that MPG
is not null, then count those cells.- Hide quoted text -

- Show quoted text -

You're welcome - thanks for feeding back.

Pete

On Aug 1, 1:41*pm, Pyrite wrote:
That works splendidly thank you. I'm not sure how it works but a couple of
re-reads of the formula and I will figure it out and add it to my repatoire
for future use.

Thanks again