COUNTIF for a range of cells in a range of worksheets?

[ser0tonin]

Please pardon the wordiness, but this is kind of an obscure problem and I cannot figure out what is making the formula return an error message...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Background: I am working on creating an Excel spreadsheet for a coworker (see attached). She is not very Excel-savvy, so I am trying to make this as user-friendly as possible for her. The workbook contains the following sheets: Combined, Template, and DoNotEdit. She is working with a number of clients and will need a new sheet for each client, which will be inserted between “Template” and “DoNotEdit.” (I inserted a sample one called “Kiddo1”) On the “Combined” sheet, I have created formulas that will add the numerical data from “Template” through “DoNotEdit”, so that as long as she has inserted the new sheet between those two, every time she adds new data it will be incorporated into sum on the “Combined” sheet.<o></o>
<o> </o>
Problem: In addition to numerical data, there is a column where she can select from a drop-down menu the condition(s) the individual she is working with has (the list of options that data validation is pulling from is on the “DoNotEdit” sheet). I would like to write a formula on the “Combined” sheet that will count the number of times a particular condition exists within a particular range of cells for a range of sheets.<o></o>
<o> </o>
This is what I came up with =COUNTIF(Template:DoNotEdit!A32:A41,"Epilepsy") and it is returning the #VALUE! Error.
I would love to both have a formula that will work and an explanation of why the one I was trying did not work, if at all possible!

Thank you!!!

 

[Robert Mika]

Hello and welcome.

You can not attached your workbook here:

You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[ser0tonin]

Sorry for the confusion -- the "see attached" was from an email when I sent the problem to a friend to try to get help!

 

[Robert Mika]

Try this:

Excel Workbook
	A	B
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40	Epilepsy
41
DONotEdit

Excel Workbook
	A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32	Epilepsy
33
Template

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1			2							Template
2										DoNotEdit
3
4
5
Combined

You need to create a Named Range referes to:
=OFFSET(Combined!$J$1,,,COUNTA(Combined!$J$1:$J$100),)

In my example I called it "list"

And tell your friends that she need to add a name of new sheets into list once she created a new sheet.