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

G

g4rod

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
 
B

Bernard Liengme

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
 
G

g4rod

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
 
B

Bob Phillips

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

etc.

--
HTH

Bob Phillips

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

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