Sumproduct assistance
 

I have this sumproduct equation

=SUMPRODUCT(--($A$10:$A$137=$A141),(IF(ISNUMBER(C$10:C$137),C$10 :C$137)))

And I'm getting ZEROS for a result if there is a blank value in the cells
C$10:C$137 that match the first condition. What do I need to change to get
it to display an NA if all of the matching cells are blank?

Thanks

Sumproduct assistance
 

You don't need an array formula

=IF(COUNT(C10:C137)=0,NA(),SUMPRODUCT(--($A$10:$A$137=$A141),--(ISNUMBER(C$10:C$137)),C$10:C$137))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Barb Reinhardt" wrote in message
...
I have this sumproduct equation

=SUMPRODUCT(--($A$10:$A$137=$A141),(IF(ISNUMBER(C$10:C$137),C$10 :C$137)))

And I'm getting ZEROS for a result if there is a blank value in the cells
C$10:C$137 that match the first condition. What do I need to change to
get
it to display an NA if all of the matching cells are blank?

Thanks

Sumproduct assistance
 

if you may need NA when all match are blank

=IF(COUNTIF(A10:A137,"="&A141)=SUMPRODUCT(($A$10:$ A$137=$A$141)*ISBLANK($C$10:$C$137)),NA(),SUMPRODU CT(--($A$10:$A$137=$A$141),(IF(ISNUMBER(C$10:C$137),C$1 0:C$137))))

pls correct some typos, adjust to suit..
maybe..
--
*****
birds of the same feather flock together..



"Barb Reinhardt" wrote:

I have this sumproduct equation

=SUMPRODUCT(--($A$10:$A$137=$A141),(IF(ISNUMBER(C$10:C$137),C$10 :C$137)))

And I'm getting ZEROS for a result if there is a blank value in the cells
C$10:C$137 that match the first condition. What do I need to change to get
it to display an NA if all of the matching cells are blank?

Thanks