software

is there a way to create a sum if statement with two criteria that sums a
different column. Sumproduct does not seem to work if there is an error #NA
in the criteria column. I have tried and tried to get it to work but I
continually get an #NA. I know that there is an error in the column but that
is not part of the criteria but sumproduct looks at it anyway. Unless I am
writing this function incorretly. Please help if you can.

=SUMPRODUCT(--($I$7:$I$2585=$AP2604),--($L$7:$L$2585=quot;Fbquot;),--(AS9:AS2587))

Column L has some NA's so sumproduct returns an NA as the answer. As you
can see, NA is not in the criteria.

Ctrl/Shift/Enter:
=SUMPRODUCT(--($I$7:$I$2585=quot;equot;),--(IF(ISNA($L$7:$L$2585),0,$L$7:$L$2585)=quot;Fbquot;),--(AS9:AS2587))

quot;Robquot; wrote:

gt; is there a way to create a sum if statement with two criteria that sums a
gt; different column. Sumproduct does not seem to work if there is an error #NA
gt; in the criteria column. I have tried and tried to get it to work but I
gt; continually get an #NA. I know that there is an error in the column but that
gt; is not part of the criteria but sumproduct looks at it anyway. Unless I am
gt; writing this function incorretly. Please help if you can.
gt;
gt; =SUMPRODUCT(--($I$7:$I$2585=$AP2604),--($L$7:$L$2585=quot;Fbquot;),--(AS9:AS2587))
gt;
gt; Column L has some NA's so sumproduct returns an NA as the answer. As you
gt; can see, NA is not in the criteria.

=SUM(IF($I$7:$I$2585=$AP2,IF(ISNUMBER(MATCH($L$7:$ L$2585,{quot;Fbquot;},0)),AS9:AS2587)))

which must be confirmed with control shift enter.

Rob wrote:
gt; is there a way to create a sum if statement with two criteria that sums a
gt; different column. Sumproduct does not seem to work if there is an error #NA
gt; in the criteria column. I have tried and tried to get it to work but I
gt; continually get an #NA. I know that there is an error in the column but that
gt; is not part of the criteria but sumproduct looks at it anyway. Unless I am
gt; writing this function incorretly. Please help if you can.
gt;
gt; =SUMPRODUCT(--($I$7:$I$2585=$AP2604),--($L$7:$L$2585=quot;Fbquot;),--(AS9:AS2587))
gt;
gt; Column L has some NA's so sumproduct returns an NA as the answer. As you
gt; can see, NA is not in the criteria.