If no matches formula displays #N/A

[Weasel]

I'm currently using this formula:

{=INDEX('Data Dump - #001'!$L$1:$L$10000,MATCH(1,('Data Dump - #001'!$H$1:$H$10000=$A8)*('Data Dump - #001'!$J$1:$J$10000=$B8)*('Data Dump - #001'!$A$1:$A$10000=$Q8)*('Data Dump - #001'!$C$1:$C$10000=$R8),0))}

What it does is looks to find a match for 4 different criteria:

('Data Dump - #001'!$H$1:$H$10000=$A8)*('Data Dump - #001'!$J$1:$J$10000=$B8)*('Data Dump - #001'!$A$1:$A$10000=$Q8)*('Data Dump - #001'!$C$1:$C$10000=$R8)

If it finds a match it then looks in the same row in column L of the Data Dump sheet and displays the number in that cell. My problem is that if there is no match excel comes back with a value of #N/A. This is a problem because I then have a sum formula adding all the values the formula finds but since there are some #N/A values the SUM formula doesn't work.

What I'm trying to figure out is if there is either a way to alter my SUM formula to ignore the #N/A values or if there's a way to change my initial formula so that if it doesn't find a match it just displays a value of 0. Thanks in advance for any help.

[NBVC]

To sum avoiding the #N/A error....

=SUMIF(A1:A100,"<>#N/A")

adjust range to suit.

[reggie1000]

hi weasel,

if you use

Please Login or Register  to view this content.

then CTRL+SHIFT & ENTER

on any error the formula will place a 0 rather than an #N/A and drag down as necessary

thanks reg

[Weasel]

Thanks for the help

[shg]

It would be thoughtful to explain which solution you chose and why ...

[Weasel]

I went with the second solution since it changed all of the values in my spreadsheet that had #N/A to 0. It made the report much cleaner to look at