countif/countifs formula to count staff absence days

[nd272]

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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date absent[/TD]
[TD]Occurrence[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]01/02/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]02/02/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]04/02/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]01/03/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]02/03/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]04/03/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kweaver]

Are your dates in dd/mm/yyyy or mm/dd/yyyy format?

 

[nd272]

hi there

thanks for replying

my dates are in dd/mm/yyyy format

 

[MARK858]

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
	A	B	C	D	E	F
1	Name	Date absent	Occurrence		Totals
2	John	01/02/2019	1		John	3
3	John	02/02/2019			Steve	2
4	John	04/02/2019	1
5	John	10/02/2019	1
6	Steve	01/03/2019	1
7	Steve	02/03/2019
8	Steve	04/03/2019	1
Sheet1

 

[kweaver]

Looks good to me!

 

[nd272]

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]

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

 

[nd272]

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 !