Sumproduct Formula Problem

[Ark68]

=SUMPRODUCT(--(R13:R91<>"0"),--(S13:S91="D*"))

Something not right withmy formula.
I am trying to get a calculated value when the cell value in column R is not equal to 0 (<> 0) AND the cell value in column S begins with a "D". ("D*")

I am getting #VALUE as a result.

 

[VoG]

Try

=SUMPRODUCT(--(R13:R91<>0),--(LEFT(S13:S91,1)="D"))

 

[Ark68]

Thank you friend,

=SUMPRODUCT(--(R13:R91<>0),--(LEFT(P13:P91,1)="D"))

Gives me #NA. I was expecting 3. (I made an incorrect range reference in my first example ... should have been column P, not S)

 

[VoG]

That formula works for me. Do you have any #N/A errors in the ranges?

 

[Ark68]

As a matter of fact ... yes.
I take it that's bad?

 

[VoG]

Yes, that is bad. Presumably those cells contain formulas like VLOOKUP that are returning #N/A. You could adjust those formulas like this

=IF(ISNA(your formula),"",your formula)

to return blanks rather than #N/A.

 

[Ark68]

Wonderful! Yes ... that worked.
Thank you VoG...

 

[T. Valko]

As a matter of fact ... yes.
I take it that's bad?

Sometimes you may want to keep the errors for whatever reason.

It can probably still be done with the errors present.

Post back if you want to pursue this and tell us which column has the errors in it. Also, what type of data is in the column when the formulas do not return #N/A. Is it text? Numbers? Could be either?