average countif - better example

[gewilson]

I was using column headers in the example below (category & rating). I want it to average all the numbers in the rating column if the category is accessibility.


=AVERAGE(COUNTIFcategory,"Accessibility")+COUNTIFrating,">0")

 

[NARAYANK991]

Hi ,


The following should give you the result you are looking for :


=AVERAGE(IF(Category="Accessibility",Rating))


where for "Category" substitute the actual range of category values , say J3:J14 , and for "Rating" substitute the actual range of rating values , say K3:K14.


This formula should be entered as an array formula , using CTRL SHIFT ENTER


Narayan

 

[prasaddn]

Hi Gewilson,


I was wondering why not use a simple builtin function called AverageIF(), and not CSE formula, its paramaters are as follows:

=AverageIF(CategoryRange, Criteria, ValueRange)

Where

CategoryRange = column which has Accessibility Categories

Criteria = you can specify the cell which has "Accessibility" word or directly enter "Accessibility"

ValueRange = Column which has Rating


Regards,

Prasad DN

 

[Luke M]

prasaddn,

I agree, AVERAGEIF would be the better function IF using 2007 or later. That function is not available in earlier versions of XL. =(