COUNTIF in multiple sheets

[ThomasClinton]

Hello,

I have an excel file with about 30 sheets, each with the name of a nurse at my hospital.

What I'd like to do is count if the field E11 in each sheet contains "NA" if a nurse has given an invalid answer to a question. If the nurse has given a valid answer the cell will have a number between 0-10.

Here is what I have so far : =SUMPRODUCT(COUNTIF(INDIRECT("Name0:Name29"!E11);"NA")).

Though I've read a lot of forum posts and this seems to work for most, it returns a #REF error for me.

Any and all help would be appreciated!

Thank you!

 

[ThomasClinton]

Good afternoon all,

I've tried all of your suggestions and nothing seems to work.

I ended up entering all the names manually in trunten's suggested formula. This didn't work initially because one of the names had an apostrophe in it. Once that was removed the count was accurate.

This isn't the most optimal solution as I now have to remember to manually enter a nurse's name when she's added to the list, but it's better than nothing.

Thank you all for your help!

 

[Eric W]

Here's another approach where you don't need to know the sheet names at all. Create 2 dummy sheets, named Start and Finish. Group the nurses' sheets together, and put the Start sheet before the first one, and the Finish sheet after the last one. Then you can use this formula:

=COUNTA(Start:Finish!E11)-COUNT(Start:Finish!E11)

The COUNTA counts the number of cells with something in them, then subtracts the number of cells with just a number. Whenever you add a new nurse, just add that sheet between Start and Finish.

 

[Sam_D_Ben]

Eric, thats cool.

 

[ThomasClinton]

Good morning Eric,

What an elegant solution! I just tried and it seems to be working flawlessly!

Thank you very much for your help. And thanks to the whole community for helping me debug this!

Cordially,

Thomas

 

[Eric W]

Glad we could help!