Counting occurances


G

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?
 
Ad

Advertisements

T

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
 
G

Guest

Biff,

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

Lauri
 
G

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
 
Ad

Advertisements

D

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.
 
G

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top