COUNT IF but not if 'X' IS NULL

[Pyrite]

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.

 

[Pete_UK]

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

 

[Pyrite]

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]

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

 

[Pyrite]

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]

You're welcome - thanks for feeding back.

Pete