Counting even if doesn't meet criteria

**Finn** · 06-23-2009, 02:42 PM

I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26. Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field.

Here is the formula/function I'm using:

=(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")

Any ideas?

**NBVC** · 06-23-2009, 02:46 PM

Does the formula I supplied here not do it for you?

http://www.excelforum.com/excel-work...v-0-error.html

**Finn** · 06-23-2009, 02:50 PM

It's a different issue than it was previously. It's a different field and formulas. it's not a #DIV/0 issue it's something with it not following the criteria I set. I most likely set it up wrong. Any help you could offer would be greatly appreciated.

**NBVC** · 06-23-2009, 02:52 PM

Or maybe you want?

=COUNTA(C2:C26)/SUMPRODUCT(--ISNUMBER(C2:C26),--(B2:B26="Yes"))

**Finn** · 06-23-2009, 03:03 PM

I've attached what I'm looking at

If i put numbers into that C2:C26 field and it says NO in B2:B26
it still factors those numbers in the math for the average at the bottom.