Counting Values across multiple sheets

[chris.slater]

Hi All
I am trying to count the number of times the letter "P" appears in the same cell reference, C3, across a number of worksheets.
The worksheets represent meeting dates and are named "Oct1", "Oct8", "Oct15", "Oct22", "Oct29", "Nov5" etc through to "Dec17".

The formula I thought should work is =COUNTIF('Oct1:Dec17'!C3,"P") but that just returns a #VALUE! error.

Anyone got any ideas about why it isn't working.

Any help gratefully received.
Many thanks.
Chris

[rcm]

countif() works only on a worksheet at a time. One could write a macro to browse the entire workbook and count the "p" apperances..

[Pete_UK]

You can't use 3-d references with COUNTIF.

One possible solution is to put the COUNTIF formula in the same cell in every sheet - a quick way of doing this is to group all those sheets together (by selecting the first tab in the sequence, then holding down the SHIFT key, then clicking on the last tab in the sequence and releasing SHIFT) and then put this formula in, say, cell Z1:

=COUNTIF(C3,"*P*")

Then ungroup the sheets by right-clicking on a sheet tab and clicking Ungroup Sheets.

Then in your summary sheet you can have a formula like:

=SUM('Oct1:Dec17'!Z1)

Hope this helps.

Pete

[SDCh]

Try this:

Please Login or Register  to view this content.

The red one is a list name of sheet

[Tony Valko]

Here's another one:

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(41548+{0,7,14,21,28,35,42,49,56,63,70,77},"mmmd")&"!C3"),"P"))

We can shorten it a bit by using a defined name.

Goto the Formulas tab>Define Name
Name: Array
Refers to: ={0,7,14,21,28,35,42,49,56,63,70,77}
OK out

Then the formula becomes:

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(41548+Array,"mmmd")&"!C3"),"P"))

[Pete_UK]

Trust you to think of that, Biff !!

Nice one.

Pete

[Tony Valko]

Thanks, Pete!

[Tony Valko]

We can shorten it a bit more...

Name: Sheets
Refers to: =TEXT(41548+Array,"mmmd")

Then the formula becomes:

=SUMPRODUCT(COUNTIF(INDIRECT(Sheets&"!C3"),"P"))

[chris.slater]

Hi SDCh
I tried the formula and it returned a #REF! error.
The actual formula now reads =SUMPRODUCT(COUNTIF(INDIRECT("'"&DEC1:OCT17&"'!C3"),"P"))
even though I put in the range &Oct1:Dec17& so there is something it doesn't like about the syntax.
I have even tried naming the range and using that in the formula but that doesn't work either.
Thanks
Chris

[chris.slater]

Hi Tony
thanks for your suggestion but you'll have to forgive my inexperience please and just give some pointers as to what certain things refer to in your formula, namely:

1. 41548
2. {0,7,14,21,28,35,42,49,56,63,70,77}
3. mmmd

Thanks for your understanding
Chris

[Pete_UK]

Hi Chris,

Tony won't be on-line for a few hours so I hope he doesn't mind if I answer for him:

1. The number 41548 is the serial number that represents the date of 1st October 2013 (put that number in a cell somewhere and format the cell as a date).

2. Those numbers (increments of 7) are added onto that start date to get other dates which are a week apart.

3. The string "mmmd" is a format string used in conjunction with the TEXT command - it converts those dates into the format OCT1, or OCT7, or OCT14 etc. to match the names of your sheets.

The formula takes each sheet name in turn and uses it within the COUNTIF function.

Hope this helps.

Pete

[Tony Valko]

Good explanation.

Thanks, Pete!

[SDCh]

The red one is a list name of sheet

What I mean is make a list name of the worksheet on one column,
on my example my list sheet on column A2 to A4 ("Oct1", "Oct8", "Oct15")


I think you must upload the example file (all sensitive data removed),
so everyone in here can give you a solution base on your condition