Sumproduct with ISNA

Hi All,

I have this formula with conditional formatting, and it works as expected. =and(sumproduct(--(isna(b2:d2)))=0,or(b2>0,c2>0,d2>0)), it works when cells b2..d2 are blank.


I am trying to understand this formula and was hoping it can be explained in plain english. I understand in general sumproduct takes two arrays, and if they are true then multiplies them as 1's and 0's and if = 1, then it takes values and adds them (hence sum of product).

What I need help in is understanding the arrays in this formula and how it works with sumproduct.


I understand that isna(b2:d2), if it is true then values in b2:d2 are not #N/A, however not clear how to interpret when = 0 , (isna(b2:d2)))=0

So going back to formula,= and(sumproduct(--(isna(b2:d2)))=0,or(b2>0,c2>0,d2>0)),

if the isna range (b2:d2) is true that it is not #N/A, because we are forcing to = 0, , then per sumproduct when value is true, it is considered a 1, now we multiply this with the 2nd half of sumproduct array, or(b2>0,c2>0,d2>0) , when any of these conditions are met i.e. b2>0, or c2>0, or d2 >0, and when these conditions are met it is also considered a 1. so 1 from first array x 1 from 2nd array= 1, so then make the cell the defined color per conditional formatting rule. is this correct interpretation?

Thanks Tom for taking the time to explain it. Appreciate it. Beginning to make sense.

A follow up question:

The SUMPRODUCT function is testing for the presence of the #N/A error value.


SUMPRODUCT(--ISNA(B2:D2))=0 or SUMPRODUCT(--(ISNA(B2:D2)))=0

What I am baffled by is it the formula only works if the cells i.e. b2 or c2 or d2 If there is a number then it works as should. If those cells were blank or empty, the formula fails, unless I press "clear content" then it works as expected, and no hidden characters either.

If I want to have it where cell contains # NA instead of a blank, then I assume it is SUMPRODUCT(--ISNA(B2:D2))<>"#N/A" correct?

Lastly im not clear as to -ISNA(B2:D2))=0, this is looking in cells b2..d2, and it's saying, if its true the cells do not contain #n/a then make that cell = 0, correct?