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

try
=AVERAGE(E2:E14,K2:K14)
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"cox ng (1)" <(E-Mail Removed)> wrote in message
news:PxDRf.241183$oG.126061@dukeread02...
>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
>

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!
==============================================


"cox ng (1)" <(E-Mail Removed)> wrote in message
news:PxDRf.241183$oG.126061@dukeread02...
> 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
>
>

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)

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"cox ng (1)" <(E-Mail Removed)> wrote in message
news:PxDRf.241183$oG.126061@dukeread02...
>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
>

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

Regards,
Gary

"RagDyer" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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!
> ==============================================
>
>
> "cox ng (1)" <(E-Mail Removed)> wrote in message
> news:PxDRf.241183$oG.126061@dukeread02...
>> 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
>>
>>
>

Further testing says NO to this.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "cox ng (1)" <(E-Mail Removed)> wrote in message
> news:PxDRf.241183$oG.126061@dukeread02...
>>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
>>
>
>

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)


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Further testing says NO to this.
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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)
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "cox ng (1)" <(E-Mail Removed)> wrote in message
>> news:PxDRf.241183$oG.126061@dukeread02...
>>>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
>>>
>>
>>
>
>