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

 

[etaf]

how do you identify a SICK occurrence
how is your data laid out ?

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[ashani]

Thanks @etaf

The data comes like this and I would like to sumif for the number of days and countif for the occurrences during the date range mentioned above

 

[etaf]

dont understand

Greater than 01/01/2025 but less than 01/02/2024

whats the date range


why does sumifs() and countifs() not work
are they REAL dates

you can use UNIQUE() and COUNTIFS() SUMIFS()

OR a Pivot table

Book1
	A	B	C	D	E	F	G
1	EMP Nane	date	Duration		name	count	Sum
2	abc	2/28/24	1		abc	1	1
3	def	1/2/25	3		def	1	3
4	ghi	1/1/25	5		ghi	1	5
5	jkl	5/28/24	4		jkl	2	11
6	abc	1/2/25	3
7	def	1/3/24	3
8	ghi	1/15/24	5
9	jkl	1/19/24	7
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=UNIQUE(A2:A9)
F2:F5	F2	=COUNTIFS($A$2:$A$9,E2,$B$2:$B$9,">=1/1/24",$B$2:$B$9,"<1/1/25")
G2:G5	G2	=SUMIFS($C$2:$C$9,$A$2:$A$9,E2,$B$2:$B$9,">=1/1/24",$B$2:$B$9,"<1/1/25")
Dynamic array formulas.

 

[ashani]

Thanks @etaf

So that's the date range for rolling 12 months and I want to data from >=01/01/2025 and <01/02/2024 hence the formula isn't working.

 

[etaf]

that range will not work
>=01/02/24 and <=01/01/25

but it cannot be greater than 1st jan 25 and also less than 1 feb 24

 

[ashani]

what would be the best option to get data for absences i.e. How many they have in January 2024 and how many they have in January 2025 combine?

 

[etaf]

jan 25 has not finished
do you want to compare the current month with a year Ago month ?
OR just add the 2 months together
based on today() date / month

so on the 1st Feb do you then want to add together Feb-24 and feb-25 , even though the month has ONLY just started

sorry a little confused as to what dates you need

based on your example what are the results you expect

 

[ashani]

Sorry for the confusion

i'm looking for How many occasion of absences an employee had in January 2024 and so far January 2025 and how many days in total as well.

so the data i'm looking for rolling 12 months month on month breakdown.

 

[etaf]

for the table i posted
count
=ROWS(FILTER($A$2:$C$9,(MONTH($B$2:$B$9)=MONTH(TODAY()))*($A$2:$A$9=E2)))
SUM
=SUM(FILTER($C$2:$C$9,(MONTH($B$2:$B$9)=MONTH(TODAY()))*($A$2:$A$9=E2)))

are those the results you expected, if so i can explain what the formula is doing
if not why not

Book1
	A	B	C	D	E	F	G
1	EMP Nane	date	Duration		name	count	Sum
2	abc	2/28/24	1		abc	1	3
3	def	1/2/25	3		def	2	6
4	ghi	1/1/25	5		ghi	2	10
5	jkl	5/28/24	4		jkl	1	7
6	abc	1/2/25	3
7	def	1/3/24	3
8	ghi	1/15/24	5
9	jkl	1/19/24	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.