sum every other cell in a row if next cell meets certain criteria
 

I am constructing a Staff hours and wages sheet for a swim school. Each day
of a month has a column with a H/T (h=helping, t=teaching) column between
each day. This shows the hours a member of staff did on each day with
whether the hours were helping or teaching on each row. At the end of each
row I would like to total the helping hours and teaching hours separately.
I've tried using sum if but it doesn't like what I'm doing, an example of the
table is

Mon h/t Tues h/t Wed h/t Thurs h/t Fri h/t Total H
Total T
Ross 2 h 2 t 1 h 3 t 0
3 5

Any help would be much appreciated

sum every other cell in a row if next cell meets certain criteria
 

It would have been so much better to use one row for H and T and another for
the hours.
Here are two User Defined Function that will work: I had Ross in A2 and the
hours and H/t data in B2:K2, so I called the functions with =hcount(B2:K2)
If you need help with Visual Basic for Applications, see
David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm



--------------------------------------------------------------------------------


Function hcount(myrange)
mytest = myrange.Count
For j = 1 To mytest
If myrange(j + 1) = "h" Then
Debug.Print myrange(j)
mycount = mycount + myrange(j)
End If
Next j
hcount = mycount
End Function

Function tcount(myrange)
mytest = myrange.Count
For j = 1 To mytest
If myrange(j + 1) = "t" Then
Debug.Print myrange(j)
mycount = mycount + myrange(j)
End If
Next j
tcount = mycount
End Function


best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"g4rod" wrote in message
...
I am constructing a Staff hours and wages sheet for a swim school. Each
day
of a month has a column with a H/T (h=helping, t=teaching) column between
each day. This shows the hours a member of staff did on each day with
whether the hours were helping or teaching on each row. At the end of
each
row I would like to total the helping hours and teaching hours separately.
I've tried using sum if but it doesn't like what I'm doing, an example of
the
table is

Mon h/t Tues h/t Wed h/t Thurs h/t Fri h/t Total H
Total T
Ross 2 h 2 t 1 h 3 t 0
3 5

Any help would be much appreciated

sum every other cell in a row if next cell meets certain crite
 

Thanks,

After thinking about it I've put the t and h categories on to separate rows
for each teacher as it makes things simpler.

Cheers

GArod

"Bernard Liengme" wrote:

It would have been so much better to use one row for H and T and another for
the hours.
Here are two User Defined Function that will work: I had Ross in A2 and the
hours and H/t data in B2:K2, so I called the functions with =hcount(B2:K2)
If you need help with Visual Basic for Applications, see
David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm



--------------------------------------------------------------------------------


Function hcount(myrange)
mytest = myrange.Count
For j = 1 To mytest
If myrange(j + 1) = "h" Then
Debug.Print myrange(j)
mycount = mycount + myrange(j)
End If
Next j
hcount = mycount
End Function

Function tcount(myrange)
mytest = myrange.Count
For j = 1 To mytest
If myrange(j + 1) = "t" Then
Debug.Print myrange(j)
mycount = mycount + myrange(j)
End If
Next j
tcount = mycount
End Function


best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"g4rod" wrote in message
...
I am constructing a Staff hours and wages sheet for a swim school. Each
day
of a month has a column with a H/T (h=helping, t=teaching) column between
each day. This shows the hours a member of staff did on each day with
whether the hours were helping or teaching on each row. At the end of
each
row I would like to total the helping hours and teaching hours separately.
I've tried using sum if but it doesn't like what I'm doing, an example of
the
table is

Mon h/t Tues h/t Wed h/t Thurs h/t Fri h/t Total H
Total T
Ross 2 h 2 t 1 h 3 t 0
3 5

Any help would be much appreciated

sum every other cell in a row if next cell meets certain criteria
 

=SUMIF(C2:M2,"h",B2:L2)

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"g4rod" wrote in message
...
I am constructing a Staff hours and wages sheet for a swim school. Each
day
of a month has a column with a H/T (h=helping, t=teaching) column between
each day. This shows the hours a member of staff did on each day with
whether the hours were helping or teaching on each row. At the end of
each
row I would like to total the helping hours and teaching hours separately.
I've tried using sum if but it doesn't like what I'm doing, an example of
the
table is

Mon h/t Tues h/t Wed h/t Thurs h/t Fri h/t Total H
Total T
Ross 2 h 2 t 1 h 3 t 0
3 5

Any help would be much appreciated