Range Across Multiple Sheets

[ShelleyBelly]

Hi There,

I'm trying to get the following equation to work across several sheets;

I would like to count the number of times a name appears given month and year. For example, Mark appears twice in july in 2010.

My spreadsheet is as follows:

A B
21-Jul-10 Mark
29-Jul-10 Pippa, Gerri, Mark, Lee, Ade
30-Jul-10 Pippa, Gerri, Nicola, Lee, Ade
3-Aug-10 Mark
4-Aug-10 Mark
5-Aug-10 Pippa, Ade, Gerri, Nicola, Jules
6-Aug-10 Pippa, Ade, Gerri, Nicola, Jules


The following formula works perfect for one sheet, but i have about 7 sheets that i need to get

=COUNTIFS(A:A,">="&DATE(2010,7,1),A:A,"<="&DATE(2010,7,31),B:B,"*"&D1&"*")

Where D1 is the name.

The following are some of the sheet names:

Jobs List FY 10_11
Jobs List FY 09_10
Jobs List FY 08_09
Jobs List FY 07_08

Any Help would be awesome, the concept seems simple but I don't seem to be able to crack it.

Cheers,

Tom

 

[tweedle]

try

=COUNTIFS(A:A,">="&DATE(2010,7,1),A:A,"<="&DATE(2010,7,31),B:B,"*"&D1&"*") +
COUNTIFS('Jobs List FY 10_11'!A:A,">="&DATE(2010,7,1),'Jobs List FY 10_11'!A:A,"<="&DATE(2010,7,31),'Jobs List FY 10_11'!B:B,"*"&D1&"*")

etc...

 

[ShelleyBelly]

Hi there,

Excellent job, an obvious answer that I couldn't see. Many thanks.

One more question though. Do you know if there are anyways to include all worksheets in a book in a formula, so as to avoid having to edit the formula when a new worksheet is made?

Many Thanks,

Tom

 

[tweedle]

mmmm, no I don't know how to do that.
One could write VBA to build a formula that would accomodate such an event though.