countif/countifs formula to count staff absence days

nd272

New Member
Joined
Jun 10, 2019
Messages
4
Hi everyone,

new to the forum and would like some guidance on some formula i am stuck on:

we have a large dataset which has some info re staff absence days , and looks like the below. what i am looking to do is increment by 1 each time the staff member in question is registered as being absent on non consecutive days.

i had tried earlier to add in a separate row with date absent column minus the earliest date recorded by employee name, which did work but meant i had to re align the date formula for each name.

the output i would hope to achieve is then build a table using all the employee names and then use the count results in the last column to do an overall result of occurnces of absences. something like a Max formula to see the occurences perhaps

any help appreciated, i am a stuck and cant get my head around how to build this one

thanks


NameDate absentOccurrence
John01/02/2019
John02/02/2019
John04/02/2019
Steve01/03/2019
Steve02/03/2019
Steve04/03/2019

<tbody>
</tbody>
 

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,344
Office Version
365, 2010
Platform
Windows, Mobile
Probably kweaver will post something tidier but seeing as I have wrote it I might as well post it. Maybe you could work with the below.

Excel Workbook
ABCDEF
1NameDate absentOccurrenceTotals
2John01/02/2019 John
3John02/02/2019Steve2
4John04/02/20191
5John10/02/20191
6Steve01/03/20191
7Steve02/03/2019
8Steve04/03/20191
Sheet1
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
673
(y) Looks good to me!
 

nd272

New Member
Joined
Jun 10, 2019
Messages
4
Mark thanks so much for this, it is exactly what i was looking for

i usually find answers somewhere on the internet that i can adapt for my own needs but in this instance i just couldnt figure it out

given that you have solved this for me i just want to make sure i have the logic correct so it will help me going forward

=IFERROR(IF(OR(B2="",A2=""),"" (if the values in either of the cells is blank then return a blank,



,IF(AND(B2=B1+1,A2=A1),"",1)),1) (this is the false part of the main iferror, both of the 2 columns will always have values so therefore this formula will always revert to this side of the formula to increment 1 at a time it takes it that if cell b9 value is +1 on the cell above and matches the A column then it returns "". then therefore if the "and" part evaluates to false it will return 1, allowing the increment to +1

i think i more or less get it, i always want to be sure of what the true logic is, as opposed to just passively copying and pasting, and it does your hard work justice as well.

am i more or less right on the above?

and thanks again


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
=SUMIF($A$2:$A8,E2,$C$2:$C8)

<tbody>
</tbody>
</body>
 

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,344
Office Version
365, 2010
Platform
Windows, Mobile
am i more or less right on the above?
Basically yes, the IFERROR is there because the first cell(B1) is text so will produce an error, the OR statement was overkill just in case :ROFLMAO: If you use formula-evaluate on cells C2,C3 and C4 you will see what it does.

Happy it helped :)
 
Last edited:

nd272

New Member
Joined
Jun 10, 2019
Messages
4
Basically yes, the IFERROR is there because the first cell(B1) is text so will produce an error, the OR statement was overkill just in case :ROFLMAO: If you use formula-evaluate on cells C2,C3 and C4 you will see what it does.

Happy it helped :)
That’s perfect I get it now, I always want to ensure I fully understand so that I take learning from it and how I could apply it to other problems going forward

Thanks again really appreciate it !
 

Forum statistics

Threads
1,078,394
Messages
5,339,939
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top