# CountIf - Multiply Pages

#### longytravel

##### Board Regular
Hey Guys,

Hope all is well

I have a homemade register for a large training course

I have multiply tables over multiply sheets (different dates). Within those sheets, column N contains the detail about whether they turned up to a session (Attended, No Show, Reduced Hours)

I would like to have a 'master sheet' that counts all the Attended, No Shows etc when the register is updated. It is made up of a drop down validation box.

The tables may grow as names are moved, added or deleted so i would like the solution to be dynamic

Any ideas?!?!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi

You just have to use the list of the worksheets that have the values, like:

=SUM(COUNTIF(INDIRECT({"Sheet1","Sheet4","Sheet7"}&"!N:N"),"Attended"))

If you need to count the values in many worksheets, it's easier to write them in a range and use it in the formula.

Thanks!!

That doesnt seem to work i'm afraid - i get #REF!

My sheets are in date form, my code is therefore =SUM(COUNTIF(INDIRECT({"19-10-11","26-10-11","07-11-11"}&"!N:N"),"Attended"))

On the 19-10-11 tab my list starts N4:N13 and then in the second table it is N17:N25 - not sure if that changes things?

Any further ideas.......??

M

Hi

As a general syntax rule, when you use a worksheet name with special characters, like a dash, you must enclose it in single quotes.

With your example use:

=SUM(COUNTIF(INDIRECT("'"&{"19-10-11","26-10-11","07-11-11"}&"'!N:N"),"Attended"))

You guys are phenomenal!!

That works a treat!

Thanks for your help - i have no idea how you learn this stuff!!

I'm glad it helped. Thanks for the feedback.

Replies
3
Views
587
Replies
0
Views
261
Replies
1
Views
396
Replies
5
Views
256
Replies
5
Views
318

1,203,514
Messages
6,055,842
Members
444,828
Latest member
StaffordStag

### 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?

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