Counting specific dates from multiple columns

[mattbird]

Hi,

I am trying to count how may time a month appear in my table, however, I have three columns. If the 1st date is cancelled then a 2nd date is given and so on. I need the 2nd invite to supersede the 1st invite and the 3rd invite to supersede the 2nd invite to produce the correct result.

Example 1: If I entre Apr in cell F2 (cell has a drop down list of months Apr to Dec), then G2 should return 2.

Example 2: If I enter Feb in cell F2, then G2 should return 3.

Also I don't want black cells to affect the count i.e. B9, as dates may be added at a later time etc.

Any help is greatly appreciated.

	A	B	C	D	E	F	G
1		1st Invite	2nd Invite	3rd Invite		Month	No
2		01/01/2023	02/04/2023
3		20/01/2023
4		18/02/2023	02/03/2023	10/04/2023
5		05/02/2023
6		10/03/20/23
7		16/02/2023	20/02/2023	27/02/2023
8		03/03/2023
9
10		01/01/2023	09/02/2023

 

[awoohaw]

I used a helper column. If someone can do without a helper column it would be great. I know you can with 365, but you don't have that.

Book1
	A	B	C	D	E	F	G
1		1st Invite	2nd Invite	3rd Invite	Last	Month	No
2		01/01/2023	02/04/2023		02/04/2023	Apr	2
3		20/01/2023			20/01/2023	feb	3
4		18/02/2023	02/03/2023	10/04/2023	10/04/2023
5		05/02/2023			05/02/2023
6		10/03/2023			10/03/2023
7		16/02/2023	20/02/2023	27/02/2023	27/02/2023
8		03/03/2023			03/03/2023
9
10		01/01/2023	09/02/2023		09/02/2023
Sheet2

Cell Formulas
Range	Formula
G2:G3	G2	=SUM(--(TEXT($E$2:$E$10,"mmm")=F2))
E2:E10	E2	=IF(SUM(--(B2:D2<>""))=0,"",MAX(B2:D2))

 

[mattbird]

Thanks for the assist,

The formula is working. I am now trying to integrate the formula into my COUNTFI formula, however I'm struggling to get it to provide the correct outcome.

{=IF($G$7="Audit Review",COUNTIFS(AR_I,G4,AR_R,"<>"),SUM(--(TEXT(AR_E,"mmm")=$A33)))}

I'm trying to get the formula to look at AR_I (I3:I7) and depending on what is selected in the drop down list (G4) it will count. then I need it to look at AR_R (R3:R7) and if it has a percentage in it to count it. finally I need it to look at AR_E (E3:E7) and count what ever month is in A33.

I have tried different variations but am not sure how to integrate the SUM formula correctly.

I hope this makes