I am trying to use the following forumla:

=SUMIF((Z2:Z44),ISTEXT(Z2:Z44),AA2:AA44)/100

I have a formula setup in AA that either has a number in it or a #NUM!.
Column Z is either blank or has a W or L in it. I want my cell to use the
above formula to add up all the numbers in column AA that have a W or L in
column Z. For some reason the above formula isn't working. It is giving me
a 0 when it should be giving me 1. Thank you.

I also tried this formula but it didn't work either

=SUMIF((Z2:Z44),(OR("W","L")),AA2:AA44)/100

Why not wrap your formula in AA in an IF() function:
IF(iserror(formula),"",formula)
this will return numeric answers or nulls and then just use sum to add up
the column.

HTH

"Jambruins" wrote:

I am trying to use the following forumla:

=SUMIF((Z2:Z44),ISTEXT(Z2:Z44),AA2:AA44)/100

I have a formula setup in AA that either has a number in it or a #NUM!.
Column Z is either blank or has a W or L in it. I want my cell to use the
above formula to add up all the numbers in column AA that have a W or L in
column Z. For some reason the above formula isn't working. It is giving me
a 0 when it should be giving me 1. Thank you.

I also tried this formula but it didn't work either

=SUMIF((Z2:Z44),(OR("W","L")),AA2:AA44)/100

perfect, thanks

"Fred" wrote:

Why not wrap your formula in AA in an IF() function:
IF(iserror(formula),"",formula)
this will return numeric answers or nulls and then just use sum to add up
the column.

HTH

"Jambruins" wrote:

I am trying to use the following forumla:

=SUMIF((Z2:Z44),ISTEXT(Z2:Z44),AA2:AA44)/100

I have a formula setup in AA that either has a number in it or a #NUM!.
Column Z is either blank or has a W or L in it. I want my cell to use the
above formula to add up all the numbers in column AA that have a W or L in
column Z. For some reason the above formula isn't working. It is giving me
a 0 when it should be giving me 1. Thank you.

I also tried this formula but it didn't work either

=SUMIF((Z2:Z44),(OR("W","L")),AA2:AA44)/100

On Mon, 27 Jun 2005 06:49:14 -0700, Jambruins
wrote:

I am trying to use the following forumla:

=SUMIF((Z2:Z44),ISTEXT(Z2:Z44),AA2:AA44)/100

I have a formula setup in AA that either has a number in it or a #NUM!.
Column Z is either blank or has a W or L in it. I want my cell to use the
above formula to add up all the numbers in column AA that have a W or L in
column Z. For some reason the above formula isn't working. It is giving me
a 0 when it should be giving me 1. Thank you.

I also tried this formula but it didn't work either

=SUMIF((Z2:Z44),(OR("W","L")),AA2:AA44)/100


You could use this *array* formula:

=SUM(((Z2:Z44="W")+(Z2:Z44="L"))*IF(ISNUMBER(AA2:A A44),AA2:AA44))

To enter an array formula, after typing or pasting it in, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula.


--ron