Hi All

I need some help with a SUMIF formula. I created a formula in a spreadsheet
to count the number of entried where the specified values in three columns
met the criteria. Having got it to work I decided to create individual
spreadsheets to be able to report on different criteria. The issue I am
having is that in some cases I get an #N/A error in some cells which is
indicating it cannot find a particular value. The formula I am using is as
follows:
SUM(IF(N$2:N$30000=B3,IF(Y$2:Y$30000="Cancel
Appointment",IF(AA$2:AA$30000="Service Provider Clinician",1,0),0)))
When I try and do a trace on these cells the Reference is showing the name
of the original spreadhseet used to create and test the formula not the
current spreadsheet.
Has anyone else come across this issue and if so how did you resolve it. An
example is shown below for ease of reference. You can see that some cells
work perfectly well and others do not.
M86617 102 St Georges Road 0
M86627 2 St Georges Road #N/A
M86619 Aldermoor Clinic 0
M86613 Allesley Park Medical Centre #N/A
M86026 Anchor Centre #N/A
M86035 Balliol Road #N/A
M86012 Barley Lea House 1
Y00140 Birmingham Road #N/A
M86008 Bredon Avenue #N/A
M86003 Broad Lane Surgery 0

Any help would be appreciated.
Kind regards
TonyK