Formula Help

[ashani]

Hi

I'm looking to count sickness occasion and number of days from the data table for each employee over the rolling 12 months. For example :

Greater than 01/01/2025 but less than 01/02/2024 during this range I want to count the following:

Column A : How many occasions employee was sick
Column B : How many days in total

Please can someone guide me with the formula. I tried using Sumproduct and Countifs but unable to get accurate detail.

Many thanks

 

[ashani]

Thanks a lot for your help @etaf

The result I'm expecting is for the specific months i.e. How many in January, 24 and January 25 in total by employees

 

[etaf]

i see that
BUT with the example you gave , which i have copied into a spreadsheet and produced some results
are they correct results as you expected

Book1
	E	F	G
1	name	count	Sum
2	abc	1	3
3	def	2	6
4	ghi	2	10
5	jkl	1	7
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=UNIQUE(A2:A9)
F2:F5	F2	=ROWS(FILTER($A$2:$C$9,(MONTH($B$2:$B$9)=MONTH(TODAY()))*($A$2:$A$9=E2)))
G2:G5	G2	=SUM(FILTER($C$2:$C$9,(MONTH($B$2:$B$9)=MONTH(TODAY()))*($A$2:$A$9=E2)))
Dynamic array formulas.

 

[ashani]

the outcome should appear like this on month on month basis for each employee.

so for example :
Employee ABC had 1 occurence of absence in January 2024 for 3 days and 1 Occurence of absence in January 2025 for 1 day, so that outcome should be 2 occurance and 4 days.

 

[etaf]

ok, so i have for ABC - 1 and 3 days , probably because you are using US based Dates
hence why i asked you to actually check the table and results i provided
and also why i suggested using XL2BB or a share - so we can make sure the results i have given WORK

so have a look AGAIN at Post 4 - and you can click on the copy ICON under the Fx icon and paste into a spreadsheet and then see what happens
as you see in my case ABC , has an entry for FEB and NOT jan - hence the difference , i think

Also I have constructed the formular BASED on today() date - we could use the heading , if its a real date and formatted to show Feb , Mar etc

if you want to create the sample and post as i mentioned in my POST 2