Sum if

[Guest]

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="Fb"),--(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.

 

[Guest]

Ctrl/Shift/Enter:
=SUMPRODUCT(--($I$7:$I$2585="e"),--(IF(ISNA($L$7:$L$2585),0,$L$7:$L$2585)="Fb"),--(AS9:AS2587))

 

[Aladin Akyurek]

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

which must be confirmed with control+shift+enter.