COUNTIF in multiple sheets

ThomasClinton

New Member
Joined
Aug 2, 2019
Messages
6
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!
 
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!
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top