countif in different columns

[cox ng \(1\)]

I wish to sum cells in two different columns and then divide that sum by
those cells that have a number greater than "0" to obtain an average. I
can do this with sucess if I remain in the same column by
=SUM(E2:E14)/COUNTIF(E2:E14,">.01").

I cannot find the proper formula if I add cells from a different column.
For example I've tried
=SUM(E2:E14,K2:K14)/COUNTIF(E2:E14,">.01")+COUNTIF(K2:K14,">.01").

Any suggestions?

Thank you for your time and help.

Regards,
Gary

 

[Don Guillett]

try
=AVERAGE(E2:E14,K2:K14)

 

[RagDyer]

You mention "greater then 0", then use ".01" in your formula.

The Average() function counts 0's, so just use your formula concept
*correctly*!
Just group your divisor by adding some parenthesis:

=SUM(E2:E14,K2:K14)/(COUNTIF(E2:E14,">.01")+COUNTIF(K2:K14,">.01"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Don Guillett]

try this ARRAY formula which must be entered with ctrl+shift+enter
=AVERAGE(IF($E$2:$E$14>0,$E$2:$E$14))+IF($K$2:$K15>0,$K$2:$K$14)

 

[cox ng \(1\)]

This formula works as I was hoping. Thank you for the help.

Regards,
Gary

 

[Don Guillett]

Further testing says NO to this.

 

[Don Guillett]

But this does by replacing + with a comma

=AVERAGE(IF($E$2:$E$14>0,$E$2:$E$14)),IF($K$2:$K15>0,$K$2:$K$14)