Home |
Search |
Today's Posts |
#1
|
|||
|
|||
COUNTIF Help
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 |
#2
|
|||
|
|||
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 "Big Rick" wrote in message ... | 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 |
#3
|
|||
|
|||
one way
highlight the column C ctrl + F3 and name the range. Use the range name in the formula HTH "Big Rick" wrote: 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 |
#4
|
|||
|
|||
Assuming that the sheet name for each month is abbreviated to three
letters, try... =SUMPRODUCT(COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(IN DIRECT("1:12")),1),"mmm ")&"!C:C"),"Sick")) Hope this helps! In article , "Big Rick" wrote: 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 |
#5
|
|||
|
|||
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" wrote: Assuming that the sheet name for each month is abbreviated to three letters, try... =SUMPRODUCT(COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(IN DIRECT("1:12")),1),"mmm ")&"!C:C"),"Sick")) Hope this helps! In article , "Big Rick" wrote: 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 |
#6
|
|||
|
|||
Taking a look at the following formula...
=SUMPRODUCT(COUNTIF(INDIRECT(TEXT(DATE(2005,ROW(IN DIRECT("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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF help | Excel Worksheet Functions | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions |