CountIf - Multiply Pages

[longytravel]

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

 

[pgc01]

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.

 

[longytravel]

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

 

[pgc01]

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"))

 

[longytravel]

You guys are phenomenal!!

That works a treat!

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

 

[pgc01]

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