sumif help...

[Guest]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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:AA44),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

 

[Guest]

perfect, thanks

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