Average formula not working

I obviously have something not correct in this formula.
I am trying to get an average of the cells above but only if they have a number in them, the cells represent the months of the year so I am trying to get what should be Jan - April / 4 and not Jan - April / 12.

I used google to try and work it out but I dont understand what I am doing wrong.


SUMIF(D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24,Z24)/(SUMPRODUCT((D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24,Z24<>0)*1))

What is in the between cells (E24 G24 I24 etc) ??
Is there a common header in another row, say 23, that indicates which columns should be included ?

Can you attach a sample file?

attached screen shot.

Try

=SUM(D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24,Z24)/COUNT(D24,F24,H24,J24,L24,N24,P24,R24,T24,V24,X24,Z24)

Will ignore blank cells

Try

=AVERAGEIF($C$16:$Z$16,"<>sales",$C$24:$Z$24)

JohnTopley, that gives the wrong answer Giving £44.72 should be £123 for the average of the 4 months.


Jonmo1, That also gives the wrong answer.

The 4 cells that with amounts are: £56, £94, £138, £205
Total = £493. Average = £123.25

Nope that also returns £44.72

Can you attach an actual Excel File ?
Pictures tend to not be helpful because we can't copy/paste the data into our own workbooks for troubleshooting.

You're getting the wrong value from all of these suggestions because those cells in row 24 that look blank, are NOT blank.
They contain SUM formulas that are returning 0's.
But you have 0's hidden because you UNchecked File - Options -Advanced - "Show a zero in cells with zero value"

Those cells STILL actually contain a 0 (it's just hidden, but it's still there)
0 Is a real value and will be counted in the Average.


Try
=AVERAGEIFS($C$24:$Z$24,$C$24:$Z$24,"<>0",$C$16:$Z$16,"<>sales")

Damn! I should have known that.

Thanks Jonmo1 your solution worked.

You're welcome.