Counting occurances
 

One of my students has a spreadsheet that they use for tracking employees
scheduled work days. Rows are the employees names, columns represent the day
of the week. The columns are repeating - Sunday through Saturday, over and
over to cover 3 months. Under the day of the week they enter a code that
signifies what shift was worked for that employee.

They want to know if there is a way to calculate how many times in that 3
month timeframe the employee worked each day of the week. So, how many
Sundays did Mary work in those three months? How many Mondays, etc.

Is this possible?

Counting occurances
 

Are the day of week headers TEXT strings?

If a person did not work on a particular day is that cell left empty?

Try this:

B1:AQ1 = day of week headers as TEXT strings: Monday, Tuesday, etc
B2:AQ2 = shift code if worked, otherwise empty

To count the number of Mondays worked:

=SUMPRODUCT(--(B1:AQ1="Monday"),--(B2:AQ2<""))

Biff

"LauriS" wrote in message
...
One of my students has a spreadsheet that they use for tracking employees
scheduled work days. Rows are the employees names, columns represent the
day
of the week. The columns are repeating - Sunday through Saturday, over
and
over to cover 3 months. Under the day of the week they enter a code that
signifies what shift was worked for that employee.

They want to know if there is a way to calculate how many times in that 3
month timeframe the employee worked each day of the week. So, how many
Sundays did Mary work in those three months? How many Mondays, etc.

Is this possible?

Counting occurances
 

Sum using multiple criteria:
http://www.officearticles.com/excel/...ft_excel.h tm
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************
"LauriS" wrote in message
...
One of my students has a spreadsheet that they use for tracking employees
scheduled work days. Rows are the employees names, columns represent the
day
of the week. The columns are repeating - Sunday through Saturday, over
and
over to cover 3 months. Under the day of the week they enter a code that
signifies what shift was worked for that employee.

They want to know if there is a way to calculate how many times in that 3
month timeframe the employee worked each day of the week. So, how many
Sundays did Mary work in those three months? How many Mondays, etc.

Is this possible?

Counting occurances
 

Biff,

THANKS!! That did the trick! I had never heard of the SUMPRODUCTS function
- learned something new!

Lauri

"T. Valko" wrote:

Are the day of week headers TEXT strings?

If a person did not work on a particular day is that cell left empty?

Try this:

B1:AQ1 = day of week headers as TEXT strings: Monday, Tuesday, etc
B2:AQ2 = shift code if worked, otherwise empty

To count the number of Mondays worked:

=SUMPRODUCT(--(B1:AQ1="Monday"),--(B2:AQ2<""))

Biff

Counting occurances
 

Anne,

Thanks for the link!

Lauri

"Anne Troy" wrote:

Sum using multiple criteria:
http://www.officearticles.com/excel/...ft_excel.h tm
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com

Counting occurances
 

=SUMPRODUCT(--(B1:AQ1="Monday"),--(B2:AQ2<""))

Ok, I'm confused. I did more research to understand the purpose of the --
and I thought I had it. But when I remove just one of the dashes I get the
same answer as when they are both there. I would have expected to get
negatives. Can anyone shed any light on this? I'm using 2000 if that makes
a difference.

Lauri

Counting occurances
 

If you removed one of each pair of minuses, then when you previously
multiplied +1 by +1 in a TRUE/TRUE condition, you'll now be multiplying -1
by -1, and of course getting the same result. If you just remove one of the
four minus signs, I would expect to get a -ve result from the SUMPRODUCT.
--
David Biddulph

"LauriS" wrote in message
...
=SUMPRODUCT(--(B1:AQ1="Monday"),--(B2:AQ2<""))

Ok, I'm confused. I did more research to understand the purpose of the --
and I thought I had it. But when I remove just one of the dashes I get
the
same answer as when they are both there. I would have expected to get
negatives. Can anyone shed any light on this? I'm using 2000 if that
makes
a difference.

Lauri

Counting occurances
 

I get it! Since I was multiplying two arrays that evaluated to T/F, it
wouldn't matter if I had both -- or just - in front of both arrays.

But if I was mutiplying a T/F array by actual numbers - THEN I would need to
use the --.

Thanks, David!

Lauri

"David Biddulph" wrote:

If you removed one of each pair of minuses, then when you previously
multiplied +1 by +1 in a TRUE/TRUE condition, you'll now be multiplying -1
by -1, and of course getting the same result. If you just remove one of the
four minus signs, I would expect to get a -ve result from the SUMPRODUCT.
--
David Biddulph

"LauriS" wrote in message
...
=SUMPRODUCT(--(B1:AQ1="Monday"),--(B2:AQ2<""))

Ok, I'm confused. I did more research to understand the purpose of the --
and I thought I had it. But when I remove just one of the dashes I get
the
same answer as when they are both there. I would have expected to get
negatives. Can anyone shed any light on this? I'm using 2000 if that
makes
a difference.

Lauri

Counting occurances
 

You're welcome. Thanks for the feedback!

SUMPRODUCT may be the 2nd most versatile function with IF being the most
versatile.

For everything you always wanted to know about SUMPRODUCT and didn't know
where to look:

http://xldynamic.com/source/xld.SUMPRODUCT.html

Biff

"LauriS" wrote in message
...
Biff,

THANKS!! That did the trick! I had never heard of the SUMPRODUCTS
function
- learned something new!

Lauri

"T. Valko" wrote:

Are the day of week headers TEXT strings?

If a person did not work on a particular day is that cell left empty?

Try this:

B1:AQ1 = day of week headers as TEXT strings: Monday, Tuesday, etc
B2:AQ2 = shift code if worked, otherwise empty

To count the number of Mondays worked:

=SUMPRODUCT(--(B1:AQ1="Monday"),--(B2:AQ2<""))

Biff