SUMPRODUCT COUNTIF (Again)

[adrian007uk]

Hi i am having a SUMPRODUCT COUNTIF problem. Previously the formula was
missing a single quotation mark between the double quotation marks. Once
this was implemented i have had no problems However, i have been asked to
make a similar sheet. The sheets have different names so i have modified the
range but i cannot get the formula to work. It is:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Universities&"'!C4"),"Pass"))

It keeps coming up with the #REF! error.

 

[Bernard Liengme]

What are the names of the sheets (or some of them)?
What is in C4?
What is the intent of the formula?--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

 

[adrian007uk]

The names of the sheets include Durham, Newcastle, Northumbria etc.

I have a master sheet that is identical to the University sheets. Therefore
C4 is the same question/statement in all sheets. The same goes for C5, C6
etc.

The basis of the formula is to count three options that could appear in a
cell (in this case pass, fail, not determined). As you can see i am
concentrating on the "Pass" cells at the moment.

The whole spreadsheet is based on a spreadsheet i made previously. A friend
asked if he could use it for this university spreadsheet but he could not get
it to work. I said i would have a look at it for him. I thought it would be
an easy job of just changing the range and the sheet names. This was causing
the error.

However, i have changed the range and named the sheets to match. I called
the range 'Schools' this time and used different cells to house the range. I
also found that there was an additional space between two words in a sheet
name (e.g., Durham University as opposed to Durham Universirty). This has
now seemed to have fixed the formula! But i do not know why? For example,
once a range has been named and used in a spreadsheet calculation (as this
sheet is an eaxct copy to mine only with different names), altering this
range is impossible, therefore you have to create a new one? Or something
else?

Adrian

 

[Bernard Liengme]

My workbooks has these sheets: Sheet1, Durham, Newcastle, York, and
Northumbria
I have opened the Name manager and added
Name: Universities
Refers to: ={"Durham","Newcastle","York","Northumbria"}
Note that I type the braces around the list of names

Now this formula
=SUMPRODUCT(COUNTIF(INDIRECT(Universities & "!C4"),"Pass"))
looks at at cells C4 in all the sheets listed above and counts how many have
the text "Pass"


best wishes