help with sumif visible cells only

[jordanburch]

=SUMPRODUCT(($AR:$AR=AR838)+0,SUBTOTAL(109,OFFSET($J:$J,ROW($J:$J)-MIN(ROW($J:$J)),0,1,1)))

hey guys for whatever reason this is returning at na value. can you help figure out whats wrong?

 

[jordanburch]

Are the NA errors not formulas?

so it is a formula and basically its refering to a list

=+VLOOKUP(AP647,'Sub Accnt xWalk'!F:H,3,FALSE)

its giving na because its not in that list. Id still like to see the na so every month I can update the list, but id also like the formula to work. This formula is in aq column then AR has a concatenate so its concatenating and returning an NA value.

 

[RoryA]

You could change the VLOOKUP formula to:

=IFERROR(VLOOKUP(AP647,'Sub Accnt xWalk'!F:H,3,FALSE),"NA")

and then the other formula should work, since there aren't any actual error values any more.​

 

[jordanburch]

You could change the VLOOKUP formula to:

=IFERROR(VLOOKUP(AP647,'Sub Accnt xWalk'!F:H,3,FALSE),"NA")

and then the other formula should work, since there aren't any actual error values any more.

you really are the excel MVP! HAHA! I didnt even think of that thanks my man!