countif/countifs formula to count staff absence days

nd272

New Member
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

 Name Date absent Occurrence John 01/02/2019 John 02/02/2019 John 04/02/2019 Steve 01/03/2019 Steve 02/03/2019 Steve 04/03/2019

<tbody>
</tbody>

kweaver

Well-known Member
Are your dates in dd/mm/yyyy or mm/dd/yyyy format?

nd272

New Member
hi there

my dates are in dd/mm/yyyy format

MARK858

Well-known Member
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
Looks good to me!

nd272

New Member
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
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 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
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 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 !