Average If one criteria across multiple columns ignoring blank cells in the column to be averaged

[Fazila]

Hi

I posted in an older thread as it helps with part of my problem but not sure if that is the right thing to do. The formula I used is from thread https://www.mrexcel.com/forum/excel...rageif-single-criterion-multiple-columns.html i.e. =SUMPRODUCT(('Updated Options'!$M$2:$P$297=A3)*('Updated Options'!$G$2:$G$297))/SUMPRODUCT(--('Updated Options'!$M$2:$P$297=A3))

However, this formula converts blank cells to having a value of 0 thus not giving a very accurate average. Is there anyway I can change the formula so it ignores blanks.

Thanks

Fazila

 

[DanteAmor]

In that same link they put these formulas:


Jonmo1
=SUMPRODUCT((B2:E33=8)*(G2:G33))/SUMPRODUCT((B2:E33=8)*(G2:G33<>0))

And

Aladin Akyurek
=AVERAGE(IF(B2:E33=8,IF(ISNUMBER(G2:G33),G2:G33)))

 

[Fazila]

In that same link they put these formulas:


Jonmo1
=SUMPRODUCT((B2:E33=8)*(G2:G33))/SUMPRODUCT((B2:E33=8)*(G2:G33<>0))

And

Aladin Akyurek
=AVERAGE(IF(B2:E33=8,IF(ISNUMBER(G2:G33),G2:G33)))

Whoops should have read the thread properly the Jonmo1 formula returned a different result to what I was expecting but Aladin Akyurek's formula worked great.

Thanks DanteAmor for pointing it out.

 

[DanteAmor]

Whoops should have read the thread properly the Jonmo1 formula returned a different result to what I was expecting but Aladin Akyurek's formula worked great.

Thanks DanteAmor for pointing it out.

I'm glad to help you. Thanks for the feedback.