Counting occurences

**RickMcc** · 03-08-2018, 11:24 PM

Hi All.

I'm running into a problem I don't know how to solve. I have set up a monthly chart to track when employees call in sick or other activities (ex. Holidays, etc...), However counting sick occurrences is proving tricky. The COUNTIF statement isn't the problem, its trying to get excel to understand what an occurrences is. If an employee calls in sick for three consecutive days, that is one occurrence. However if an employee calls in sick on Monday, back to work on Tuesday, calls in sick on Wednesday, back to work on Thursday, and calls in sick on Friday, that is 3 occurrences. How do I get excel to handle that?

Thank you for any help you can provide

**RickMcc** · 03-08-2018, 11:53 PM

One other question. If I mark down a person used 4 hours of sick time as S4, how do I count this as an "S" with a COUNTIF?

**RickMcc** · 03-09-2018, 07:28 PM

Using the wildcard search I'm using the following formula:

COUNTIFS(C8:BL9,"S*",C12:BL13,"S*",C16:BL17,"S*",C20:BL21,"S*",C24:BL25,"S*",C28:BL29,"S*",C32:BL33,"S*",C36:BL37,"S*",C40:BL41,"S*",C44:BL45,"S*",C48:BL49,"S*",C52:BL53,"S*")

However I keep coming up with a total of Zero. Any help is appreciated. Thank you

**kroko** · 03-09-2018, 10:43 PM

RickMcc,

In this case you can use =COUNTIF(range,"S*")

**kroko** · 03-09-2018, 10:47 PM

RickMcc,

in this case you can use the following:

=COUNTIF(C48:BK48,"S*")+COUNTIF(C52:BK52,"S*")+....

When you use "countifs" Ms Excel will consider it as 2nd, 3rd, ... condition so to return the result it will check all condition, but in your case what you want is Ms Excel to count every month and sum it up. So "+" will help you.

**kroko** · 03-09-2018, 11:52 PM

RickMcc,

i have reviewed your attached file and i do have something i don't really understand:
1-Why you need to have one blank column from day to another day?
2-You want to have number of consecutive days? which it is normally not run that way.

however i think you can try this formula:
=COUNTIFS(C8:BL8,"eS",E8:BN8,"<eS")

above formula will skip not to count as 2 or 3 or... if eS is coming as consecutive.. please let me know if you still have some error.

it do took me sometime to come with this solution :-)

**JeteMc** · 03-13-2018, 04:12 PM

Here is a 'low tech' possibility.
1) Populate the the rows that previously showed only the days of the week with dates using =B7 for the 1st of the month and =C7+1 for the cells to the right (custom format ddd).
2) Populate column BM using:

Formula:

Please Login or Register  to view this content.

3) Populate column BN using: =IF(AND(BM6<>"",BM6<>SUM(BM5,1)),1,0)
4) Populate cell H56 using: =SUM(BN6:BN371)
Let us know if you have any questions.

**RickMcc** · 03-13-2018, 05:52 PM

kroko,

Thank you very much!

**RickMcc** · 03-13-2018, 05:53 PM

Originally Posted by kroko

=COUNTIF(C48:BK48,"S*")+COUNTIF(C52:BK52,"S*")+....

This worked! Thank you

**RickMcc** · 03-13-2018, 06:27 PM

kroko,

The reason for multiple columns is to allow for multiple types of leaves to be used on the same date. For example if an employee was out sick, but only had 2 hours of sick time available to use. Then one "box" would be for the sick time. The other three boxes would be used to account for the remaining 6 hours (example: vacation for 3 hours & unapproved leave 3 hours). If a box is not used, it grays out.

I'm trying to make sure consecutive sick days count as 1, not the actual number of days used. Example if employee called in 3 days in a row, it counts as 1 occurrence rather than 3. If the same employee called in sick 1 day, was back at work the next, then out sick the following day, that would be 2 occurrences.

I really do appreciate your time and effort. Thank you =)

**jeffreybrown** · 03-13-2018, 09:46 PM

RickMcc,

Please don't quote whole posts -- it's just clutter.

If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

**RickMcc** · 03-13-2018, 09:47 PM

Whoops sorry

**RickMcc** · 03-13-2018, 10:30 PM

Thanks everyone for you help

I'm still having trouble getting this to work they way I need it to. If cells C8, C9, C10 all have a value of "S*", C11 is blank, C12 has a value of "H", and C13 has a value of "S*", how do I get this to equal 2 occurrences of "S*" in Cell E60?

**JeteMc** · 03-14-2018, 09:19 AM

Is that the calendar attached to post #1?
I assume that you are saying three consecutive sick days, a day without notation, a holiday and then another sick day.
The proposed solution in Post #7 counts that as two occurrences.
Let us know if you have any questions.

**kroko** · 03-14-2018, 09:38 PM

Did you try this formula

Formula:

Please Login or Register  to view this content.

It will calculate base on your requirement (for January row) and you just change the row for other months..

**RickMcc** · 03-15-2018, 06:05 PM

Kroko,

=COUNTIFS(C8:BL8,"S*",E8:BN8,"<S*")

works except it won't recognize "S2" as an occurrence of "S*". The "S2" would indicate the use 2 hours of Sick time. How do I get to recognize "S2"?

By the way, thank you for your help

**RickMcc** · 03-16-2018, 08:30 PM

JeteMC,

Sorry for delay. Work is getting hectic. Ref solution in #7, when I use it, it calculates the 1st row for the month correctly, but ignores the 2nd row. Also, it appears the formatting for the days of the week is no longer working (if the day cell (Sun, Mon, Tue, etc...) matches either of the DO days, then an "O" should appear, if not then an "R")

**kroko** · 03-16-2018, 11:05 PM

Hi! great that work!
but i don't understand your problem that it doesn't recognize "S2" because i test here it do recognize.

for example if
1 2 3 4 5 6 7
SF S2 R SF R R R => then formula will show result as 2
SF SF R S2 R R R => then result is 2 either
SF R S2 S2 R R R => then result also 2

sorry i try to attached file but probably my internet is too slow.
so it doesn't meet your requirement?
please let me know

**JeteMc** · 03-17-2018, 12:14 PM

Made a few changes:
1) The formula that populates the cells with either 'R' or 'O' is:

Formula:

Please Login or Register  to view this content.

2) Added two cells that display the weekday number in S1:S2 (Monday being the second day of the week and Tuesday the third)
3) Modified the formula in column BM to read:

Formula:

Please Login or Register  to view this content.

4) Modified the formula in column BN to read:

Formula:

Please Login or Register  to view this content.

Notice: the formula for 'R' or 'O' has only been applied to Jan and Feb.
Notice: The formula in BM now finds S's in either the 1st or 2nd row of each month.
Notice: The formula in BN assumes the Monday and Tuesday are weekends, and takes sick days that occur on consecutive workdays as being one event. I.E. sick on Sunday and Wednesday.
Notice: The formula in BN counts sick days on the last workday of one month and the first workday of the next as being one occurrence. I.E. sick on April 29th and May 2nd.
Let us know if you have any questions.

**RickMcc** · 03-20-2018, 05:24 PM

You Rock! Thank you very much!

**JeteMc** · 03-21-2018, 08:18 AM

You're Welcome. Thank You for the feedback (to whomever it is intended) and for marking the thread as 'Solved'. I hope that you have a blessed day.

Counting occurences

LinkBack

Thread Tools

Rate This Thread

Display

Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Re: Counting occurences

Thread Information

Users Browsing this Thread

Similar Threads

Counting no of occurences.

Counting occurences of a string

Counting Time Occurences

counting occurences

Counting the occurences

Counting Occurences

Counting Occurences

[SOLVED] Counting Number of Occurences

Bookmarks

Bookmarks

Posting Permissions