Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
SUMPRODUCT Assistance | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |