Adding up values after checking a column

**Cmorgan** · 11-03-2011, 04:32 PM

Evening,

Hopefully my last problem if someone can help me out

I'm using the formula

=COUNTIF(INPUT!$H1:$H163,"<>"&B14)

to count all the values in column H which are NOT B14.

I now want to work out the average grade in column BK for those cells where the value in column H is not B14?

I tried:

=SUMPRODUCT((INPUT!H2:$H163,"<>"&B14)*INPUT!$BK$2:$BK$163)/COUNTIF(INPUT!H2:$H163,"<>"&B14)

But it comes back with an error.

Any ideas would be greatly appreciated

Many thanks

**Paul** · 11-03-2011, 05:32 PM

Hi Cmorgan,

In Excel 2007+ you can use AVERAGEIF (or AVERAGEIFS for multiple criteria). For example:

=AVERAGEIF(INPUT!$H$2:$H$163,"<>"&B14,INPUT!$BK$2:$BK$163)

**Cmorgan** · 11-03-2011, 05:46 PM

Thanks for this, I'm using 2010.

I've tried this and the figure comes in at 31.24, when it has to be a figure between 1-5. Any ideas?

I appreciate the help.

**Paul** · 11-03-2011, 05:49 PM

I'd suggest uploading a copy of your workbook (less any sensitive data, e.g. names, addresses) so we can take a look.

In the file, show what you expect the result to be and why you expect that.

**Cmorgan** · 11-04-2011, 03:18 AM

Apologies, must have been my brain was frazzled by Excel, the previous formula does work, I had just used an incorrect column.

Thanks ever so much for your help!