SUMIF Formula not working

[TonyK]

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

 

[macropod]

Hi Tony,

Your problem cells probably contain formulae referencing the other workbook. You could do a Find/Replace, with the Find string being
the name of the other workbook. Once you find the first instance, copy the whole path & filename, plus the sheet name & trailing
exclamation mark and use that as the Find expression, with nothing as the Replace expression.

Also, your formula looks like it could be simplified:
=SUM(IF((N$2:N$30000=B3)*(Y$2:Y$30000="Cancel Appointment")*(AA$2:AA$30000="Service Provider Clinician"),1,0))