Counting occurances

[Guest]

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?

 

[T. Valko]

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

 

[Anne Troy]

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

 

[Guest]

Biff,

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

Lauri

 

[Guest]

Anne,

Thanks for the link!

Lauri

 

[Guest]

=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

 

[David Biddulph]

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.

 

[Guest]

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

 

[T. Valko]

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