Timesheet COUNT function based on Hours inputted.

Question

I have a timesheet that I want to calculate the total days worked where the time sheet is based on hours. There are 5 rows where a person could input their type of work. (IE- work A on the first row and work B on the second row). The columns are numbered by days. I have tried using a COUNT function but if a person inputs hours on row A and row B, this is double counting the days. How would my total days formula work? I essentially need to count the colunms if they are greater than 1? How would I do that?

I hope the below example works as an illustration. If I just do a count based on the 1,4,2 hours that were inputted under day #2. I would get 3 total days. How could I write a formula that would only count the number of days were the entire day column is greater than 0?

                    Days

             1        2         3        4        5

H -A             1.0hrs

O -B             4.0hrs

U -C             2.0hrs

R

S

Answer 1

The simplest answer would be to put a row in for daily totals and just count on that row.

alternatively you could check each column and sum up the results

Assuming that Hours A Day 1 data is in cell B3

=(SUM(B3:B7)>0)+(SUM(C3:C7)>0)+(SUM(D3:D7)>0)+(SUM(E3:E7)>0)+(SUM(F3:F7)>0)

When the check for each column is false the value is 0 when the check is true the value is 1.

Answer 2

The simplest thing would be to add a formula for each day, like

=IF(COUNTA(B2:B6)<>0,1,0)

copied across, and then sum up those formulas.