Countifs help! Count visible cells which have been date greater than or less than todays date

[amysh]

Hi! I'm very new to formulas on excel and learning as I go and by googling a lot!

I have a spreadsheet full of staff names and training completion dates. I have hidden staff who are on long term sick or maternity leave and want to create a formula to count how many staff are currently in date and how many are out of date so that I can then ultimately work out the percentage of the full team who are in date. Can anyone help me with this?

I used =SUBTOTAL to calculate how many staff are visible after hiding those on LTS and maternity but I don't know how to proceed after this

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data (using fake names)
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[amysh]

Example data selection.xlsx
	A	B	C
1	First name	surname	First Aid Expiry
2	Morgan	Finch	Jul-22
3	Ayla	Skywalker	Aug-22
4	Candice	Gump	Mar-22
5	Albert	Scrooge	Jun-22
7	Coraline	Weasley	Apr-22
8	Angelica	Fletcher	Dec-21
9	Ellexis	Chase	Oct-22
10	Molly	Huxley	Nov-21
12	Total Staff	10
13	Total Visible Staff	8
14	Total with dates more than today	5
Sheet1

Cell Formulas
Range	Formula
B12	B12	=COUNTIF(B2:B11, "*")
B13	B13	=SUBTOTAL(103,B2:B11)
B14	B14	=COUNTIFS(C2:C11,">"&TODAY())

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C10	Cell Value	>"today"	text	NO
C2:C11	Cell Value	>=NOW()	text	NO
C2:C11	Cell Value	<=NOW()	text	NO

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D
1	First name	surname	First Aid Expiry
2	Morgan	Finch	01/07/2022	1
3	Ayla	Skywalker	01/08/2022	1
4	Candice	Gump	01/03/2022	1
5	Albert	Scrooge	01/06/2022	1
6	Bob	Lucas	01/07/2022	1
7	Coraline	Weasley	01/04/2022	1
8	Angelica	Fletcher	01/12/2021	1
9	Ellexis	Chase	01/10/2022	1
10	Molly	Huxley	01/11/2021	1
11	Joe	Biden	08/10/2022	1
12	Total Staff	10
13	Total Visible Staff	10
14	Total with dates more than today	5
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=SUBTOTAL(103,A2)
B12	B12	=COUNTIF(B2:B11, "*")
B13	B13	=SUBTOTAL(103,B2:B11)
B14	B14	=COUNTIFS(C2:C11,">"&TODAY(),D2:D11,1)

Fluff.xlsm
	A	B	C	D
1	First name	surname	First Aid Expiry
2	Morgan	Finch	01/07/2022	1
3	Ayla	Skywalker	01/08/2022	1
4	Candice	Gump	01/03/2022	1
5	Albert	Scrooge	01/06/2022	1
7	Coraline	Weasley	01/04/2022	1
8	Angelica	Fletcher	01/12/2021	1
9	Ellexis	Chase	01/10/2022	1
10	Molly	Huxley	01/11/2021	1
12	Total Staff	10
13	Total Visible Staff	8
14	Total with dates more than today	3
Sheet1

Cell Formulas
Range	Formula
D2:D5,D7:D10	D2	=SUBTOTAL(103,A2)
B12	B12	=COUNTIF(B2:B11, "*")
B13	B13	=SUBTOTAL(103,B2:B11)
B14	B14	=COUNTIFS(C2:C11,">"&TODAY(),D2:D11,1)

 

[amysh]

Thank you so much!! Hours of my life saved!!

 

[Fluff]

You're welcome & thanks for the feedback.