Count the same month across multiple columns

[UserI]

Hi,
I've got a worksheet with employees in col. A and then their latest 5 salary increases.
Col. B=salary increase effective date 1; col. C=salary increase reason 1; col. D=salary increase % 1; col. E=salary increase effective date 2; col. F=salary increase reason 2; col. G=salary increase % 2; col. H=salary increase effective date 3 and so on.
I need a formula to count how many times a certain month, let's say September, will appear for each individual as effective month for salary change, considering that there could be blanks if no data and the columns with dates are not consecutive.
Thank you in advance for your help.
Best regards.

 

[SanjayGMusafir]

Sounds interesting to work on, but two things -

1. First upload a sample data using XL2BB to work upon. Can't go through the hazard of recreating a sample data and then working on a formula to test it.
2. Second, how to decide which month is to be tested for an employee as it may vary, though, generally it seldom varies in an organization.

 

[bebo021999]

Use header to choose which columns are dates, then get month, then sum
Here is a sample:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Name	Effective date	Reason	%	Effective date	Reason	%		Effective date	Reason	%			Effective date	Reason	%		9	<<< month
2	Peter	01 January 2017	xxx	10%	01 September 2019	xxx	10%		05 September 2021	xxx	10%			01 August 2022	xxx	10%		2	<<< count
3	John	05 May 2021	xxx	5%	05 May 2021	xxx	5%		05 May 2021	xxx	5%			05 May 2021	xxx	5%		0	<<< count
Sheet1

Cell Formulas
Range	Formula
R2:R3	R2	=SUM(($B$1:$P$1="Effective date ")*IFERROR(MONTH($B2:$P2)=R$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[UserI]

New Microsoft Excel Worksheet.xlsx
	A
1	Reference month: November 2022
Sheet1

 

[UserI]

Sounds interesting to work on, but two things -

1. First upload a sample data using XL2BB to work upon. Can't go through the hazard of recreating a sample data and then working on a formula to test it.
2. Second, how to decide which month is to be tested for an employee as it may vary, though, generally it seldom varies in an organization.

New Microsoft Excel Worksheet.xlsx
	A
1	Reference month: November 2022
Sheet1

Hi,

Here is a sample of data.
The reference month is depending when the data it's checked, so we can say it's random.
If I want to check in Novemebr how many salary changes were in July, I wan to be able to do so.

Thank you for your support.
BR

 

[UserI]

Use header to choose which columns are dates, then get month, then sum
Here is a sample:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Name	Effective date	Reason	%	Effective date	Reason	%		Effective date	Reason	%			Effective date	Reason	%		9	<<< month
2	Peter	01 January 2017	xxx	10%	01 September 2019	xxx	10%		05 September 2021	xxx	10%			01 August 2022	xxx	10%		2	<<< count
3	John	05 May 2021	xxx	5%	05 May 2021	xxx	5%		05 May 2021	xxx	5%			05 May 2021	xxx	5%		0	<<< count
Sheet1

Cell Formulas
Range	Formula
R2:R3	R2	=SUM(($B$1:$P$1="Effective date ")*IFERROR(MONTH($B2:$P2)=R$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you for your reply.
This formula is not working for me.

Best regards