Countif
I have a spreadsheet where I want to count the number of sick days for
individuals. So I have the name of the person in a column and the date for the year in a row Name/Date 1/1 ......10/02.......20/08......30/08....12/09 An Other S s P Peter S I want to count the number of "S" for each person so on seperate tab I want Name Number of Sick Days AN Other 2 P Peter 1 Any Suggestions thanks Andrew |
Countif
Hi Andrew
If your list on Sheet2 is in the same order as the data on Sheet 1, then on Sheet2 cell B2 enter =COUNTIF('Sheet1'!B2:IV2,"S") Copy down column B for the extent of entries in column A -- Regards Roger Govier "Andrewllan" wrote in message ... I have a spreadsheet where I want to count the number of sick days for individuals. So I have the name of the person in a column and the date for the year in a row Name/Date 1/1 ......10/02.......20/08......30/08....12/09 An Other S s P Peter S I want to count the number of "S" for each person so on seperate tab I want Name Number of Sick Days AN Other 2 P Peter 1 Any Suggestions thanks Andrew |
Countif
There is a beautiful template for this in Execel Templates from Microsoft
Templates Business and Legal Meetings and Projects Agendas and Minutes http://office.microsoft.com/en-us/te...CT061995981033 - This doesn't need much editing to disply what you are asking. Hope this helps "Andrewllan" wrote: I have a spreadsheet where I want to count the number of sick days for individuals. So I have the name of the person in a column and the date for the year in a row Name/Date 1/1 ......10/02.......20/08......30/08....12/09 An Other S s P Peter S I want to count the number of "S" for each person so on seperate tab I want Name Number of Sick Days AN Other 2 P Peter 1 Any Suggestions thanks Andrew |
Countif
The sort of thing I was looking for was
Lookup( Tab2(a1) (If (Tab2(a1)= Tab1(a1) and Any of Tab1(a2:a365="S") count number of "S"'S) I do not know how else to describe this Thanks Andrew "Roger Govier" wrote: Hi Andrew If your list on Sheet2 is in the same order as the data on Sheet 1, then on Sheet2 cell B2 enter =COUNTIF('Sheet1'!B2:IV2,"S") Copy down column B for the extent of entries in column A -- Regards Roger Govier "Andrewllan" wrote in message ... I have a spreadsheet where I want to count the number of sick days for individuals. So I have the name of the person in a column and the date for the year in a row Name/Date 1/1 ......10/02.......20/08......30/08....12/09 An Other S s P Peter S I want to count the number of "S" for each person so on seperate tab I want Name Number of Sick Days AN Other 2 P Peter 1 Any Suggestions thanks Andrew |
Countif
Hi Andrew
Then try =SUMPRODUCT((Sheet1!A2=Sheet2!A2)*(Sheet1!B2:IV2=" S")) Copy down as appropriate -- Regards Roger Govier "Andrewllan" wrote in message ... The sort of thing I was looking for was Lookup( Tab2(a1) (If (Tab2(a1)= Tab1(a1) and Any of Tab1(a2:a365="S") count number of "S"'S) I do not know how else to describe this Thanks Andrew "Roger Govier" wrote: Hi Andrew If your list on Sheet2 is in the same order as the data on Sheet 1, then on Sheet2 cell B2 enter =COUNTIF('Sheet1'!B2:IV2,"S") Copy down column B for the extent of entries in column A -- Regards Roger Govier "Andrewllan" wrote in message ... I have a spreadsheet where I want to count the number of sick days for individuals. So I have the name of the person in a column and the date for the year in a row Name/Date 1/1 ......10/02.......20/08......30/08....12/09 An Other S s P Peter S I want to count the number of "S" for each person so on seperate tab I want Name Number of Sick Days AN Other 2 P Peter 1 Any Suggestions thanks Andrew |
All times are GMT +1. The time now is 05:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com