SUMIF(INDIRECT to ignore #N/A

[jimmisavage]

Good morning,
I'm struggling to get my formula to work. I know it should look something like this but it doesn't seem to like me

=SUMIF(INDIRECT("'"&$D$5&" Patients'!V2:V3,"<>#N/A")

 

[Aladin Akyurek]

What do you have in D5? Is V2:V3 the range you want to sum?

 

[steve the fish]

Your indirect has gone a bit wrong there. You have missed the closing inverted comma and bracket. Its:

INDIRECT("'"&$D$5&" Patients'!V2:V3")

There is also no sum range so i believe it will just use the criteria range.

 

[jimmisavage]

D5 has a name, such as Dave - This, in turn should refer to a sheet called Dave Patients and the sum should then add the values in V2:V301 (not sure why it's only showed V3) but when there is an #N/A in that range i get an error. otherwise it works fine.

 

[jimmisavage]

I think I've cracked it thank you

=SUMIF(INDIRECT("'"&$D$5&" Patients'!V2:V301"),"<>#N/A")

 

[Aladin Akyurek]

D5 has a name, such as Dave - This, in turn should refer to a sheet called Dave Patients and the sum should then add the values in V2:V301 (not sure why it's only showed V3) but when there is an #N/A in that range i get an error. otherwise it works fine.

=SUM(SUMIF(INDIRECT("'"&$D$5&" Patients'!V2:V3"),{"<0",">0"}))