Counting dates

[asnedge1]

Hi
We have an excel spreadsheet recording health & safety data across the company.


We'd like to show the count of site inspections undertaken each month in the Scorecard1 tab. The data is held on the SHEQ-Inspections tab in column B.

Can anyone help with a formula for this please?

 

[saboh12617]

Hello,

With month number in cell C1

=SUMPRODUCT(1*(MONTH(B:B)=C1))

 

[HighAndWilder]

Hello,

With month number in cell C1

=SUMPRODUCT(1*(MONTH(B:B)=C1))

Check to see what this returns when the month number is 1.

 

[saboh12617]

Check to see what this returns when the month number is 1.

? Works fine for me.

 

[Fluff]

? Works fine for me.

That's because you've changed the formula.
I also suspect that it should also take into account the year.

 

[HighAndWilder]

That's because you've changed the formula.
I also suspect that it should also take into account the year.

It seems to work if a table column is used or range but not the entire column.

It doesn't like blank cells. It calculates these as month 1.

Sample Data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				1	2	3	4	5	6	7	8	9	10	11	12		Date
2	1	20/01/2024		1048564	1	1	2	2	0	1	0	2	0	2	1		20/01/2024
3	2	18/07/2024		1	1	1	2	2	0	1	0	2	0	2	1		18/07/2024
4	3	26/11/2024															26/11/2024
5	4	26/03/2024		3	1	1	2	2	0	1	0	2	0	2	1		26/03/2024
6	5	18/05/2024															18/05/2024
7	6	24/04/2024															24/04/2024
8	7	09/11/2024															09/11/2024
9	8	23/09/2024															23/09/2024
10	9	15/02/2024															15/02/2024
11	10	09/04/2024															09/04/2024
12	11	18/05/2024															18/05/2024
13	12	15/12/2024															15/12/2024
14	13	09/09/2024															09/09/2024
15
16
17
Sheet2

Cell Formulas
Range	Formula
E1:O1	E1	=D1+1
D2:O2	D2	=SUMPRODUCT(1*(MONTH($B:$B)=D1))
D3:O3	D3	=SUMPRODUCT(1*(MONTH(Table20[Date])=D1))
D5	D5	=SUMPRODUCT(1*(MONTH(B2:B16)=D1))
E5:O5	E5	=SUMPRODUCT(1*(MONTH($B:$B)=E1))
A3:A14	A3	=A2+1

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet2!$D$1:$O$3	E1, D5, D2:D3

 

[saboh12617]

Oh thank you very much for the precision. Indeed, I only tested it on a table, and on the full column but for month>1...
What a pity. If you guys have better solutions, i thought this one was smart & small, but it is actually too small

 

[HighAndWilder]

Hi
We have an excel spreadsheet recording health & safety data across the company.


We'd like to show the count of site inspections undertaken each month in the Scorecard1 tab. The data is held on the SHEQ-Inspections tab in column B.

Can anyone help with a formula for this please?

Cab you submit the chart and data sheet using XL2BB as it is not clear where your chart data comes from?