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?

 

[RoryA]

It's really not a good idea to use entire columns in a formula like that. As to your error, I suspect you have an error value in one of the referenced cells.

 

[jordanburch]

It's really not a good idea to use entire columns in a formula like that. As to your error, I suspect you have an error value in one of the referenced cells.

so i tried this
=SUMPRODUCT(($AR2:$AR3000=AR838)+0,SUBTOTAL(109,OFFSET($J2:$J3000,ROW($J2:$J3000)-MIN(ROW($J2:$J3000)),0,1,1)))

and it still didnt work. Theres no NA in the data. Theres an na in column ar but not in ar838. AR 838 is a formula if that matters?

 

[RoryA]

If the N/A is within

$AR2:$AR3000 then it will stop your formula from working. Assuming it's as a result of a formula, is there a reason you can't error handle it?​

 

[jordanburch]

If the N/A is within
$AR2:$AR3000 then it will stop your formula from working. Assuming it's as a result of a formula, is there a reason you can't error handle it?

even though its not looking for that value?

Im not sure how to error handle. The message says

a value is not available to the formula or function.

 

[jordanburch]

If the N/A is within
$AR2:$AR3000 then it will stop your formula from working. Assuming it's as a result of a formula, is there a reason you can't error handle it?

ok so changing those nas to 0 worked. Thanks for your help! now i have to figure out why the zeros.

Jordan

 

[jordanburch]

If the N/A is within
$AR2:$AR3000 then it will stop your formula from working. Assuming it's as a result of a formula, is there a reason you can't error handle it?

Hi Rory,

Is there any way to account for NA's in the formula? I am having the same issue when I run the code and dont really want to make all of the NA's to 0's. any thoughts?

 

[RoryA]

They don't have to be 0, they could be anything other than an error - eg "" or even "Error" since that would just be text.

 

[jordanburch]

They don't have to be 0, they could be anything other than an error - eg "" or even "Error" since that would just be text.

but once i redo next months recons there will be an NA for sure so the code will error out again, is there a way to account for the na in the formula? Or should I just filter for na and change it to something else?

 

[RoryA]

Are the NA errors not formulas?