# Averageif again formula is counting blank cells

1. ## Averageif again formula is counting blank cells

I have another averageif scenario

the formula I have is as follows;

=AVERAGE(IF(((Properties!S3:S1002="Y")+(Properties!S3:S1002="A")),Properties!BY3:BY1002))

Column S may have blank cells but the corresponding cell (same row) in column BY may be populated. I can't get it to not take into account any cell in column BY that is populated but blank in column S.

Any help would be much appreciated! (PS will read carefully and follow instruction properly this time).  Register To Reply

2. ## Re: Averageif again formula is counting blank cells

Hello Allen,

Apart from an unnecessary set of parentheses (which won't affect the result) that formula looks OK to me.

It's an "array formula" though which means that you need to confirm with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.....if you don't do that results may be unpredictable....  Register To Reply

3. ## Re: Averageif again formula is counting blank cells

that's exactly what I though. I know what the answer should be which is £6,642.43. If I use the formula in post #1, I get £6,709.21 which includes £20k in the average range in column BY which does not have a corresponding Y or A in column S.

If I make it an array, I get the following £4,305.68 hmmm.

I have attached a copy of the sheet which I am working on as my testbed. The formula is on sheet "Kitchen Completions" cell ref: P22.

If you could have a look, that would be great.  Register To Reply

4. ## Re: Averageif again formula is counting blank cells

OK, I see the problem, it's actually the other way round, you have some rows where the column S value is "Y" but the BY value is blank, e.g. rows 6, 7 and 8. In some circumstances (including here) the blanks are interpreted as zeros so they are throwing off the average.

You can amend the array formula so that it only averages non-blank rows in column BY, i.e.

=AVERAGE(IF((Properties!S3:S1002="Y")+(Properties!S3:S1002="A"),IF(Properties!BY3:BY1002<>"", Properties!BY3:BY1002)))

confirmed with CTRL+SHIFT+ENTER

Note: if you have any zero values in that column (where the column S criteria is also met) then they will be included in the average, if you don't want that then change <>"" to <>0 and that will exclude blanks and zeroes.

You could also use a "non-array" formula (in Excel 2007 and later) by using a combination of SUMIF and COUNTIFS, i.e.

=SUM(SUMIF(Properties!S3:S1002,{"Y","A"},Properties!BY3:BY1002))/SUM(COUNTIFS(Properties!S3:S1002,{"Y","A"},Properties!BY3:BY1002,"<>"))  Register To Reply

5. ## Re: Averageif again formula is counting blank cells

Genius, pure genius! Absolutely spot on!!!!!!!!!   I don't need to worry about zeros in column BY as data is entered here manually once we know what the initial value is, only columns BZ and CA that I have to worry about zeros.

Thank you for your help, very much appreciated!  Register To Reply

6. ## Re: Averageif again formula is counting blank cells

I thought that I was going to be clever with this and just apply the formula you kindly provided to the following columns BZ and CA

But nope, it doesn't seem to be that straightforward. Column BZ does need to included blanks and zeros giving a result of £11.72.

Theoretically, column CA sould be the same as BY with any values from BZ added but i must be doing something wrong as I get £4,313.27 instead of £6,654.14.

Sorted Column CA just changed "<>" to ">0" still a little stuck on column BZ  Register To Reply