occurence count

[shank]

I've got a row representing 31 days in a month.
If a worker calls in sick, the hours missed goes in that cell.
That could be any number up to 8 hours.
That absence may carry into many days.
I need to calculate the number of occurences.
How many times did they call in sick that month?

Assume worker calls in sick: 3rd,4th,5th

Assume worker calls in sick: 8th

Assume worker calls in sick: 21st,22nd

That's 3 occurences.

What's the simplest formula to show 3 occurences?

thanks!

 

[T. Valko]

One way:

Assume A1:AE1 are days of the month
A2:AE2 is where you enter the hours missed (if any) otherwise the cells are
empty.

Enter this formula in A3:

=IF(A2>0,1,"")

Enter this formula in B3 and copy across to AE3:

=IF(B2="","",IF(AND(B2>0,A2>0),"",MAX($A3:A3)+1))

That will show the number of occurrences. You can put the formulas in an out
of sight area of the sheet and then use a MAX() formula to get the
occurrences.

Biff

 

[T. Valko]

Even better:

=SUMPRODUCT(--(A2:AD2<>""),--(B2:AE2=""))+(AE2<>"")

Biff

 

[shank]

I have the Day numbers in: E2:AI2
The sick row: E4:AI4
I tried: =SUMPRODUCT(--(E2:AI2<>""),--(E4:AI4<>""))+(AI4<>"")
But it counts the totals days off. That's not what I need.

I need a count of the "occurrences" of sick calls.
Assume sick calls...
Days:
1__2__3__4__5__6__7__8__9__10__11__12__13__14__15__16__17__18__19__20__21__22__23__24__25__etc....
Sick: 8__8__4___________8________8____8
The above person had 3 occurrences.
I need to account the "groups": 8,8,4 = 1 group... 8 = 1 group... 8,8 = 1
group... TOTAL 3 occurrences.
What formula would do that?

thanks!

 

[T. Valko]

I tried: =SUMPRODUCT(--(E2:AI2<>""),--(E4:AI4<>""))+(AI4<>"")

Try this:

=SUMPRODUCT(--(E4:AH4<>""),--(F4:AI4=""))+(AI4<>"")

Notice how the ranges are "staggered": (E4:AH4<>"") (F4:AI4="")

That's needed for this to work!

Biff

 

[shank]

=SUMPRODUCT(--(E4:AH4<>""),--(F4:AI4=""))+(AI4<>"")
Notice how the ranges are "staggered": (E4:AH4<>"") (F4:AI4="")

This works! But, could you explain in plain english what this is doing?
SUMPRODUCT in the help section is used to sum an array of cells, but the
above is all on one row. What do these do "--"? I appreciate the help!
thanks!

 

[T. Valko]

Even though all the data is in 1 row we've created 2 arrays by staggering
the references:

A1 B1 C1
.......B1 C1 D1

Basically, all this is doing is testing a series of 2 cells. It tests 1 cell
to see if it is not empty then it tests the cell next to it to see if it is
empty. Where those 2 conditions are TRUE the "--" converts the TRUE to a 1
and where those 2 conditions are FALSE the "--" converts the FALSE to a 0.
Then Sumproduct justs adds up all the 1's and 0's to arrive at the result.

I just thought of something. What if a person misses Friday and the
following Monday? Is that a single instance or is that 2 instances? If
that's considered 2 instances then there's no problem. If that's considered
1 instance then the formula will count that as 2 instances. One way to fix
that is to enter something (anything, like a "x") in the cells for Saturday
and Sunday.

Biff