COUNTIF Help

[Guest]

My file has 13 sheets in it. One called Holidays, the others Apr to Mar.
In Holidays, I want a simple number of how many sick days taken.

With help from a previous post, I have changed my formula of
=COUNTIF(C5:C11,"Sick")+COUNTIF(C13:C19,"Sick")etc (5 blocks per sheet) to
=COUNTIF(C:C),"Sick")
which will count the whole column, although this will obviously only work if
it is on the individual sheet itself.

Please can you help me change it to the total number in of sick days taken
in the whole 12 months. This is to be put in the Holidays sheet.

Thanking you in anticipation

 

[Jim Rech]

You are either going to have to have a COUNTIF on each sheet and then sum
those cells on the summary sheet or use a formula like this:

=COUNTIF(Sheet1!C:C,"a")+COUNTIF(Sheet2!C:C,"a")+....

--
Jim
| My file has 13 sheets in it. One called Holidays, the others Apr to Mar.
| In Holidays, I want a simple number of how many sick days taken.
|
| With help from a previous post, I have changed my formula of
| =COUNTIF(C5:C11,"Sick")+COUNTIF(C13:C19,"Sick")etc (5 blocks per sheet) to
| =COUNTIF(C:C),"Sick")
| which will count the whole column, although this will obviously only work
if
| it is on the individual sheet itself.
|
| Please can you help me change it to the total number in of sick days taken
| in the whole 12 months. This is to be put in the Holidays sheet.
|
| Thanking you in anticipation
| --
| Big Rick

 

[Guest]

one way
highlight the column C > ctrl + F3 and name the range. Use the range name in
the formula
HTH

 

[Domenic]

Assuming that the sheet name for each month is abbreviated to three
letters, try...

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(INDIRECT("1:12")),1),"mmm
")&"!C:C"),"Sick"))

Hope this helps!

 

[Guest]

Jim Rech, Ray A, and Domenic. A million thanks.

But Domenic, any chance explaining how your formula works. I was gobsmacked
when it worked first time!
For example, Why use indirect, date, 2005 and mmm. What if it wasn't a
timesheet and it was maybe a golf handicap. Would date, 2005 and mmm still be
used. Whilst this might be simple for you, to me I'm baffled.

Thanking everyone again for all your help
Big Rick

 

[Domenic]

Taking a look at the following formula...

=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(INDIRECT("1:12")),1),"mmm
")&"!C:C"),"Sick"))

....here's how it breaks down:

ROW(INDIRECT("1:12")) returns the following array of numbers...

1
2
3
..
..
..
12

....which is used as the second argument for the DATE function.

DATE(2005,ROW(INDIRECT("1:12")),1) returns...

1/1/05
2/1/05
3/1/05
..
..
..
12/1/05

TEXT(DATE(2005,ROW(INDIRECT("1:12")),1),"mmm") returns...

Jan
Feb
Mar
..
..
..
Dec

INDIRECT(TEXT(DATE(2005,ROW(INDIRECT("1:12")),1),"mmm")&"!C:C") gives
you...

Jan!C:C
Feb!C:C
Mar!C:C
..
..
..
Dec!C:C

Note that INDIRECT returns a reference specified by a text string.

COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(INDIRECT("1:12")),1),"mmm")&"!C:C"),"
Sick") gives you...

COUNTIF(Jan!C:C,"Sick")
COUNTIF(Feb!C:C,"Sick")
COUNTIF(Mar!C:C,"Sick")
..
..
..
COUNTIF(Dec!C:C,"Sick")

Each COUNTIF returns a result, one for each month. SUMPRODUCT then sums
the results.

Hope this helps!