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!

 

[trunten]

This should​ work. Let me know:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Name0";"Name29"}&"'!E11"),"NA"))

 

[ThomasClinton]

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!

 

[Sam_D_Ben]

Thomas, I guess you should add all your sheet names in Truntens' function.

I guess so.

Excel 2013/2016
	A
1	3
Sheet1

Cell Formulas
Range	Formula
A1	=SUMPRODUCT(COUNTIF(INDIRECT({"Name0";"Name1";"Name2"}&"!E11"),"N/A"))

 

[ThomasClinton]

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!

 

[ThomasClinton]

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!

 

[Sam_D_Ben]

I tried, It should work.

 

[Fluff]

Check you haven't miss typed one of the sheet names.

 

[Fluff]

Another option is to make a list of your sheetnames somewhere & use
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A15&"'!B10"),"N/A"))

 

[Sam_D_Ben]

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Name0";"Name29"}&"'!E11"),"NA"))

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