I am new to this forum but thanks for any help.

I have one sheet which is a work schedule of 6 weeks at a time. On the
second sheet I was trying to get some totals of the individual employees. The
totals that I was trying to get were for: mon - wed totals, thurs totals, and
fri totals by themselves. The way that I was trying it was by each individual
employee. So for instance, employee Bob: how many times Bob's name appears in
the mon-wed columns, thurs column, and fri column and then sum those totals.
I dont know if I should use functions: if, sum, sumif, countif or a
different. My sheet 2 is referencing or linking to sheet 1 which is my
schedule.
Example:
Mon Tues Wed Thurs Fri
Bob Pat Fred Bob Bob
Pat Lisa Bob Fred Bob

so Mon-wed Bob=2
Thurs Bob=1
Fri. Bob=2
Sorry for the long post just wanted to convey my situation as accurate and
descriptive as possible. Thanks again.

"Michael" wrote in message
...
I am new to this forum but thanks for any help.

I have one sheet which is a work schedule of 6 weeks at a time. On the
second sheet I was trying to get some totals of the individual employees.
The
totals that I was trying to get were for: mon - wed totals, thurs totals,
and
fri totals by themselves. The way that I was trying it was by each
individual
employee. So for instance, employee Bob: how many times Bob's name appears
in
the mon-wed columns, thurs column, and fri column and then sum those
totals.
I dont know if I should use functions: if, sum, sumif, countif or a
different. My sheet 2 is referencing or linking to sheet 1 which is my
schedule.
Example:
Mon Tues Wed Thurs Fri
Bob Pat Fred Bob Bob
Pat Lisa Bob Fred Bob


If that is in the range A1 to E3 then:
COUNTIF(A2:C3,"Bob")

will give you 2 which is what you want. Make sure however you always spell
each name the same way - no stray spaces before or after the letters.

You can use a simple COUNTIF over the entire range of cells. So, for your
first example, COUNTIF($A$2:$C$3,A5)

Whe
Mon = A1, Fri = E1

Bob (your countif critieria) is A5
If "Pat" is in A6, then you could just extend the formula down and it would
count all of the instances of "Pat" in Monday - Wednesday
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Michael" wrote:

I am new to this forum but thanks for any help.

I have one sheet which is a work schedule of 6 weeks at a time. On the
second sheet I was trying to get some totals of the individual employees. The
totals that I was trying to get were for: mon - wed totals, thurs totals, and
fri totals by themselves. The way that I was trying it was by each individual
employee. So for instance, employee Bob: how many times Bob's name appears in
the mon-wed columns, thurs column, and fri column and then sum those totals.
I dont know if I should use functions: if, sum, sumif, countif or a
different. My sheet 2 is referencing or linking to sheet 1 which is my
schedule.
Example:
Mon Tues Wed Thurs Fri
Bob Pat Fred Bob Bob
Pat Lisa Bob Fred Bob

so Mon-wed Bob=2
Thurs Bob=1
Fri. Bob=2
Sorry for the long post just wanted to convey my situation as accurate and
descriptive as possible. Thanks again.

Thanks Lorne and M Kan for your replies. I am still not getting mine to
work. I hope that you can help or give me some insight. Here is the code I
put in under range:
=countif(Sheet1!B10:D10:Sheet1!B21:D21:Sheet1!B32: D32:Sheet1!B43:D43:Sheet1!B54:D54:Sheet1!B65:D65)
At first I had a , between sheet ranges like: Sheet1!B10:D10,Sheet1!B21:D21...
it gave me an error. Then I changed the , to a : and it was able to run and
calculate but the result is way off. It gives me a 13 when it should be 2. I
dont know what I am doing wrong. Am I able to reference that many ranges or
not?
So here is what my schedule looks like:
Mon Tues Wed Thurs Fri
Bob Pat Lisa Fred Bob week 1
{there is some other cells here that I dont want counted}
Pat Lisa Fred Dave Pat week 2
{there is some other cells here that I dont want counted}
Bob Fred Dave Pat Lisa week 3
so I have 6 weeks of this. countif(A1:C1,A3:C3,A5:C5; "Bob") should equal 2
I might not be making myself clear. I am trying to count multiple weeks(6)
at a time for my totals between Mon-wed, then thurs totals, fri totals. I
know it is confusing.

Try like this:

=SUM(COUNTIF(INDIRECT({"A1:C1","A3:C3","A5:C5"})," Bob"))

or
=SUMPRODUCT((A1:C1="Bob")+(A3:C3="Bob")+(A5:C5="Bo b"))

or
=SUMPRODUCT((MOD(ROW(A1:C5),2)=1)*(A1:C5="Bob"))


"Michael" wrote:


Thanks Lorne and M Kan for your replies. I am still not getting mine to
work. I hope that you can help or give me some insight. Here is the code I
put in under range:
=countif(Sheet1!B10:D10:Sheet1!B21:D21:Sheet1!B32: D32:Sheet1!B43:D43:Sheet1!B54:D54:Sheet1!B65:D65)
At first I had a , between sheet ranges like: Sheet1!B10:D10,Sheet1!B21:D21...
it gave me an error. Then I changed the , to a : and it was able to run and
calculate but the result is way off. It gives me a 13 when it should be 2. I
dont know what I am doing wrong. Am I able to reference that many ranges or
not?
So here is what my schedule looks like:
Mon Tues Wed Thurs Fri
Bob Pat Lisa Fred Bob week 1
{there is some other cells here that I dont want counted}
Pat Lisa Fred Dave Pat week 2
{there is some other cells here that I dont want counted}
Bob Fred Dave Pat Lisa week 3
so I have 6 weeks of this. countif(A1:C1,A3:C3,A5:C5; "Bob") should equal 2
I might not be making myself clear. I am trying to count multiple weeks(6)
at a time for my totals between Mon-wed, then thurs totals, fri totals. I
know it is confusing.

Thanks Tm for those formulas. They work and it saved me a lot of typing. I
appreciate your help.

"Teethless mama" wrote:

Try like this:

=SUM(COUNTIF(INDIRECT({"A1:C1","A3:C3","A5:C5"})," Bob"))

or
=SUMPRODUCT((A1:C1="Bob")+(A3:C3="Bob")+(A5:C5="Bo b"))

or
=SUMPRODUCT((MOD(ROW(A1:C5),2)=1)*(A1:C5="Bob"))


"Michael" wrote:


Thanks Lorne and M Kan for your replies. I am still not getting mine to
work. I hope that you can help or give me some insight. Here is the code I
put in under range:
=countif(Sheet1!B10:D10:Sheet1!B21:D21:Sheet1!B32: D32:Sheet1!B43:D43:Sheet1!B54:D54:Sheet1!B65:D65)
At first I had a , between sheet ranges like: Sheet1!B10:D10,Sheet1!B21:D21...
it gave me an error. Then I changed the , to a : and it was able to run and
calculate but the result is way off. It gives me a 13 when it should be 2. I
dont know what I am doing wrong. Am I able to reference that many ranges or
not?
So here is what my schedule looks like:
Mon Tues Wed Thurs Fri
Bob Pat Lisa Fred Bob week 1
{there is some other cells here that I dont want counted}
Pat Lisa Fred Dave Pat week 2
{there is some other cells here that I dont want counted}
Bob Fred Dave Pat Lisa week 3
so I have 6 weeks of this. countif(A1:C1,A3:C3,A5:C5; "Bob") should equal 2
I might not be making myself clear. I am trying to count multiple weeks(6)
at a time for my totals between Mon-wed, then thurs totals, fri totals. I
know it is confusing.