Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Highlight a row automatically if a cell meets certain criteria | Excel Worksheet Functions | |||
Get data if cell within a row meets criteria | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) |