Nested count if

[Wishmaster89]

Hi Everyone

This is my first post here.

I have a formula where i am a list of learners, teacher names and an acheivement date. What i want to do is count how many learners are assigned to each teacher, thats the easy part -

=COUNTIF(B:B,G2)

If the learner does have an achievement date they need to stay in the teachers total figures until 1 month after their achievement date.
So column H needs to do a count of learners with no achievement date and those with an achievement date and up to 1 month after. Here is my attempt so far. Do i need to use Let?

=IF(COUNTIF(B:B,"<>"),IF(TODAY()>EOMONTH(D:D,1),"",COUNTIF(B:B,G2)),COUNTIF(B:B,G2))

Formula_v2.xlsx
	A	B	C	D	E	F	G	H
1	learner ID	Teacher Name		achievement date			teacher	totals
2	1	Sam					Sam	#VALUE!
3	2	Sam		2/1/22			Bob	#VALUE!
4	3	Bob					Gary	#VALUE!
5	4	Bob					James	#VALUE!
6	5	Gary		12/1/21			Ben	#VALUE!
7	6	Bruce
8	7	Gary
9	8	Gary		11/1/21
10	9	Bruce
11	10	Sam
12	11	James		4/1/22
13	12	Ben		12/1/21
14
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=IF(COUNTIF(B:B,"<>"),IF(TODAY()>EOMONTH(D:D,1),"",COUNTIF(B:B,G2)),COUNTIF(B:B,G2))
H6	H6	=IF(COUNTIF(B:B,"<>"),IF(TODAY()>EOMONTH(D:D,1),"",COUNTIF(B:B,G6))*COUNTIF(B:B,G6))

On a separate note, can anyone please tell me why "value is false" in the formula bar is always greyed out, it happens all the time for me, I am using Mac OS version.

 

[alansidman]

Easily achieved with Power Query also called Get and Transform Data and found on the Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Teacher Name"}, {{"Count of Students", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[Wishmaster89]

Thanks but i don't have that tab, i am using excel on mac. Is there no way to it with a normal formula?

 

[alansidman]

Possibly, but not in my wheelhouse. Good Luck. I'm out.

BTW: I offered that solution as you had indicated in your profile that you were using Windows operating system.

 

[KenU]

With cell I1 = TODAY(), you can use the following formula beginning in H2 and copied down as needed:
=SUMPRODUCT(($G2=$B:$B)*(($I$1<=$D:$D)+($D:$D=0)))

Hope that helps!

Regards,
Ken

 

[Wishmaster89]

With cell I1 = TODAY(), you can use the following formula beginning in H2 and copied down as needed:
=SUMPRODUCT(($G2=$B:$B)*(($I$1<=$D:$D)+($D:$D=0)))

Hope that helps!

Regards,
Ken

Hi Ken

That is amazing thank you so much. One question though, even though it does work, I don't understand how the code is saying to not count a learner 1 month after the acheivement date,

$I$1<=$D:$D)

It is just saying less than or equal to right?

 

[Eric W]

Another:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	learner ID	Teacher Name		achievement date			teacher	totals
2	1	Sam					Sam	3
3	2	Sam		2/1/2022			Bob	2
4	3	Bob					Gary	1
5	4	Bob					James	1
6	5	Gary		12/1/2021			Ben	0
7	6	Bruce					Bruce	2
8	7	Gary
9	8	Gary		11/1/2021
10	9	Bruce
11	10	Sam
12	11	James		4/1/2022
13	12	Ben		12/1/2021
Sheet5

Cell Formulas
Range	Formula
H2:H7	H2	=COUNTIFS($B$2:$B$13,G2,$D$2:$D$13,">="&TODAY()-30)+COUNTIFS($B$2:$B$13,G2,$D$2:$D$13,"")

 

[KenU]

You are right I did overlook the one month adjustment.

Ken