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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Good morning trunten,

Thank you very much for your response. I no longer have the #REF error so there's definitely some improvement.

However, the formula only seems to be counting the "NA"s in the first (Name0) and last sheet (Name29), without taking into consideration what's in between (Name1 through Name28).

Any idea what might be causing this? Thanks again!
 
Upvote 0
Thomas, I guess you should add all your sheet names in Truntens' function.

I guess so.


Excel 2013/2016
A
13
Sheet1
Cell Formulas
RangeFormula
A1=SUMPRODUCT(COUNTIF(INDIRECT({"Name0";"Name1";"Name2"}&"!E11"),"N/A"))
 
Upvote 0
Good morning Sam,

Though this is a viable option and, what I'll likely do if nothing else works, I'd much rather do it automatically rather than type each name individually.

I did try enterning the names manually for a few and it does in fact work.

Thanks again!
 
Upvote 0
Good morning again,

I entered all the names in trunten's function and have the same #REF error as before, which leads me to believe that the reference error is somewhere in my sheets. Do you have any suggestions of what I should look for? I've checked all the E11 cells one by one and found that they do in fact contain either an NA or a number.

I'm at a loss.

Thank you!
 
Upvote 0
Another option is to make a list of your sheetnames somewhere & use
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A15&"'!B10"),"N/A"))
 
Upvote 0
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Name0";"Name29"}&"'!E11"),"NA"))

Could be the stuff highlighted in red giving you the error.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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