Countifs formula with criteria comparing date with year

[RockEd]

Hello,

I am trying to write a formula that counts for every time a person appears in a table whilst also checking whether they appear in the selected year.

First sheet ('enter data'):

Case number	Person	Date signed up
1	Person A	01/10/2021
2	Person A	01/08/2021
3	Person A	01/01/2022
4	Person B	01/10/2021

Second sheet: this is what I want it to look like:

Year (drop down)	List of people	Case count for year
2021	Person A	2
	Person B	1

So the formula I have in column C in the second table is currently (that is wrong):

=IF(B2="",0,((COUNTIFS(IM_case,$B2,TM_ests_month_list,$A$2))))

Where IM_Case an offset formula that effectively tracks column A above; if you must know:

=OFFSET('Enter data'!$$2,0,0,COUNTA('Enter data'!$A:$A)-1,1)

and TM_ests_month_list is a similar offset formula that tracks column C in the first sheet:

=OFFSET('Enter data'!$C$2,0,0,COUNTA('Enter data'!$A:$A)-1,1)

However I know this is wrong because it is comparing the full dates of say 01/10/2021 against the date of 2021 (which I believe would default to 01/01/2021). I somehow need to insert a formula similar to this:

=IF(YEAR(A2)=YEAR(TM_ests_month_list),[value if true], [value if false])

But I can't get my head around how to do that! Can someone help please?

 

[Legacy 143009]

Sheet2 C2:

=COUNTIFS('Sheet1'!$C:$C, 'Sheet2'!$A$2, 'Sheet1'!$B:$B, 'Sheet2'!$B2)

I didn't understand the rest, sorry.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Case number	Person	Date signed up
2	1	Person A	01/10/2021		2021	Person A	2
3	2	Person A	01/08/2021			Person B	1
4	3	Person A	01/01/2022
5	4	Person B	01/10/2021
Update

Cell Formulas
Range	Formula
G2:G3	G2	=COUNT(FILTER($C$2:$C$1000,(YEAR($C$2:$C$1000)=$E$2)*($B$2:$B$1000=F2)))

 

[RockEd]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Case number	Person	Date signed up
2	1	Person A	01/10/2021		2021	Person A	2
3	2	Person A	01/08/2021			Person B	1
4	3	Person A	01/01/2022
5	4	Person B	01/10/2021
Update

Cell Formulas
Range	Formula
G2:G3	G2	=COUNT(FILTER($C$2:$C$1000,(YEAR($C$2:$C$1000)=$E$2)*($B$2:$B$1000=F2)))

Thank you, however that doesn't quite work for me because the year (in this case cell E2) is actually 01/01/2021 (with the formatting of the cell being custom category of YYYY).

If it is more hassle to change it then please say, and I will remove the date and just put in a 'general' number equal to the year; however I'd prefer to keep it as a proper date if at all possible..

thank you!!

 

[Fluff]

In that case you can use

=COUNT(FILTER($C$2:$C$1000,(YEAR($C$2:$C$1000)=year($E$2))*($B$2:$B$1000=F2)))