Possible COUNTIF question across 12 Tabs

[Little Bean]

Hi. I have been having a browse for an answer to this question, but my solutions aren't working.

I have 12 tabs with figures, & a 13th tab with totals & average. The calculation for the total is below, but I would like a 2nd box to give me an average across the same 12 figures but only counting the ones that are not zero value

=Jan!Q3+Feb!Q3+Mar!Q3+Apr!Q3+May!Q3+Jun!Q3+Jul!Q3+Aug!Q3+Sept!Q3+Oct!Q3+Nov!Q3+Dec!Q3

Hope that makes sense, & thanks

 

[C Moore]

not pretty but this would return number of non-zero months, so previous sum divided by non-zero months would be avereage:
=previous sum/(if(Jan!Q3>0,1,0)+if(Feb!Q3>0,1,0)...+if(Dec!Q3>0,1,0))

 

[Aladin Akyurek]

Like this:

=SUM(Jan:Dec!Q3)/INDEX(FREQUENCY(Jan:Dec!Q3,0),2)

 

[Tetra201]

Like this:

=SUM(Jan:Dec!Q3)/INDEX(FREQUENCY(Jan:Dec!Q3,0),2)

Nice, but works only for positive numbers, which may/may not be the case.

 

[Aladin Akyurek]

It seems to me admissible to average negatives only or positives only or all numbers, but not both negatives and positives with the exclusion of zeros.

 

[Little Bean]

Thx Aladin, Will let you know the result. It is only positive numbers, so should work a dream.

 

[Little Bean]

Like this:

=SUM(Jan:Dec!Q3)/INDEX(FREQUENCY(Jan:Dec!Q3,0),2)

I tried this today and not sure if I'm doing something wrong, but I just get an invalid formula error

 

[Aladin Akyurek]

I tried this today and not sure if I'm doing something wrong, but I just get an invalid formula error

See the workbook which implements the suggestion: https://www.dropbox.com/s/tvzhbkm4le6ig67/Little Bean average of positives across 12 tabs .xlsx?dl=0