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

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

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

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

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