Sumproduct assistance

B

Barb Reinhardt

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
 
B

Bob Phillips

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)
 
D

driller

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(),SUMPRODUCT(--($A$10:$A$137=$A$141),(IF(ISNUMBER(C$10:C$137),C$10:C$137))))

pls correct some typos, adjust to suit..
maybe..
 
Top