Auto Sum when there are #Num! Values in the way

Hi all,

I have a workbook with a bunch of formulas. One specific formula

=IF(OR(AND(D$10>=$B$2,D$10<=$C$2),OR(D$10=$B$3,D$10=$C$3)),INDEX(Data!F$2:F$1698,SMALL(IF(ISNUMBER((SEARCH($B$5,Data!$D$2:$D$1698))*(SEARCH($D$5,Data!$A$2:$A$1698))),ROW(Data!F$2:F$1698)-MIN(ROW(Data!F$2:F$1698))+1,""),ROW(C2))))

Returns #NUM! When the value isn't present. Because of this I used conditional formatting to white out all #num values, even though they are there.

Now I need to auto sum in the area where the NUM# values are present... except when I press auto sum, it returns #num!

How would i fix this?

Try:

=IFERROR(IF(OR(AND(D$10>=$B$2,D$10<=$C$2),OR(D$10=$B$3,D$10=$C$3)),INDEX(Data!F$2:F$1698,SMALL(IF(ISNUMBER((SEARCH($B$5,Data!$D$2:$D$1698))*(SEARCH($D$5,Data!$A$2:$A$1698))),ROW(Data!F$2:F$1698)-MIN(ROW(Data!F$2:F$1698))+1,""),ROW(C2)))),"")

thank you so much. worked like a charm

Logit said:

Try:

=IFERROR(IF(OR(AND(D$10>=$B$2,D$10<=$C$2),OR(D$10=$B$3,D$10=$C$3)),INDEX(Data!F$2:F$1698,SMALL(IF(ISNUMBER((SEARCH($B$5,Data!$D$2:$D$1698))*(SEARCH($D$5,Data!$A$2:$A$1698))),ROW(Data!F$2:F$1698)-MIN(ROW(Data!F$2:F$1698))+1,""),ROW(C2)))),"")

Click to expand...

You are very much welcome !

Glad to help.