HOW TO COUNT HOW MANY BLANKS and NON BLANKS I HAVE IN MONTH

[AYSHANA]

good day

im trying to count how many blanks and non blanks i have per month

Book1
	A	B	C	D	E	F	G
1	UNIT ALLOCATED IN SYSTEM	UNIT ISSUED IN SYSTEM			2023
2	15-02-23 9:50		WITHOUT BLANK	WITH BLANK		01-01-23	01-02-23
3	15-02-23 14:44		0	0		31-01-23 23:59	28-02-23 23:59
4	15-02-23 14:44	15-02-23 15:41
5	14-02-23 17:38	14-02-23 18:16
6	16-02-23 10:56	16-02-23 11:00
7	15-02-23 23:51	16-02-23 0:02
8	17-02-23 8:58	17-02-23 9:06
9	19-02-23 9:40	19-02-23 13:24
10	19-02-23 9:40
11	19-02-23 5:56
12	18-02-23 19:44
Sheet1

Cell Formulas
Range	Formula
F2	F2	=DATE($E$1,ROWS($F$2:$F$2),ROWS($F$2:$F$2))
G2	G2	=DATE($E$1,ROWS($F$2:$F3),ROWS($F$2:$F$2))
F3:G3	F3	=EOMONTH(F2,0)+"23:59"
C3:D3	C3	=IF($F$2<=TODAY(),COUNTIFS(B2:B12,">="&$U$2,B2:B12,"<"&$F$3,B2:B12,""),"")

 

[awoohaw]

try this:

mr excel questions 12.xlsm
	A	B	C	D	E
1	UNIT ALLOCATED IN SYSTEM	UNIT ISSUED IN SYSTEM			Target Date:
2	Sun 2023-01-15 09:50		WITHOUT BLANK	WITH BLANK	2023-02-01
3	Wed 2023-02-15 14:44		6	3
4	Wed 2023-02-15 14:44	Wed 2023-02-15 15:41
5	Tue 2023-02-14 17:38	Tue 2023-02-14 18:16
6	Thu 2023-02-16 10:56	Thu 2023-02-16 11:00
7	Wed 2023-02-15 23:51	Thu 2023-02-16 00:02
8	Fri 2023-02-17 08:58	Fri 2023-02-17 09:06
9	Sun 2023-02-19 09:40	Sun 2023-02-19 13:24
10	Sun 2023-02-19 09:40
11	Sun 2023-02-19 05:56
12	Sat 2023-03-18 19:44
Sheet24

Cell Formulas
Range	Formula
C3	C3	=SUM((--($A$2:$A$12>=$E$2))*(--($A$2:$A$12<(1+EOMONTH($E$2,0))))*(--(B2:B12>0)))
D3	D3	=SUM((--($A$2:$A$12>=$E$2))*(--($A$2:$A$12<(1+EOMONTH($E$2,0))))*(B2:B12=""))

 

[AYSHANA]

its not working

Book1
	A	B	C	D	E	F
1	UNIT ALLOCATED IN SYSTEM	UNIT ISSUED IN SYSTEM
2	15-02-23 9:50		WITHOUT BLANK	WITH BLANK
3	15-02-23 14:44		0	0
4	15-02-23 14:44	15-02-23 15:41
5	14-02-23 17:38	14-02-23 18:16				01-02-23
6	16-02-23 10:56	16-02-23 11:00
7	15-02-23 23:51	16-02-23 0:02
8	17-02-23 8:58	17-02-23 9:06
9	19-02-23 9:40	19-02-23 13:24
10	19-02-23 9:40
11	19-02-23 5:56
12	18-02-23 19:44
Sheet1

Cell Formulas
Range	Formula
C3	C3	=SUM((--($A$2:$A$12>=$F$5))*(--($A$2:$A$12<(1+EOMONTH($F$5,0))))*(--(B2:B12>0)))
D3	D3	=SUM((--($A$2:$A$12>=$F$5))*(--($A$2:$A$12<(1+EOMONTH($F$5,0))))*(B2:B12=""))

 

[awoohaw]

Maybe it is your excel version....
When Entering the formula, instead of using then [Enter] key, use [Cntl][SHFT][Enter], or use the SUMPRODUCT Function instead of SUM.

=SUMPRODUCT((--($A$2:$A$12>=$F$5))*(--($A$2:$A$12<(1+EOMONTH($F$5,0))))*(--(B2:B12>0)))

=SUMPRODUCT((--($A$2:$A$12>=$F$5))*(--($A$2:$A$12<(1+EOMONTH($F$5,0))))*(B2:B12=""))

 

[AYSHANA]

it works ,
but for (with blanks) its not counting still 0 , even i did ctrl shift enter

 

[AYSHANA]

why the counting for the blank showing 3 instead of 5

 

[awoohaw]

you'll need to show me what you have?

 

[awoohaw]

I did notice one thing. The cells in the last expression are not absolute referenced. This is updated:

mr excel questions 12.xlsm
	A	B	C	D	E	F
1	UNIT ALLOCATED IN SYSTEM	UNIT ISSUED IN SYSTEM
2	Wed 2023-02-15 09:50		WITHOUT BLANK	WITH BLANK
3	Wed 2023-02-15 14:44		6	5
4	Wed 2023-02-15 14:44	Wed 2023-02-15 15:41
5	Tue 2023-02-14 17:38	Tue 2023-02-14 18:16				Wed 2023-02-01 00:00
6	Thu 2023-02-16 10:56	Thu 2023-02-16 11:00
7	Wed 2023-02-15 23:51	Thu 2023-02-16 00:02
8	Fri 2023-02-17 08:58	Fri 2023-02-17 09:06
9	Sun 2023-02-19 09:40	Sun 2023-02-19 13:24
10	Sun 2023-02-19 09:40
11	Sun 2023-02-19 05:56
12	Sat 2023-02-18 19:44
Sheet25

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT((--($A$2:$A$12>=$F$5))*(--($A$2:$A$12<(1+EOMONTH($F$5,0))))*(--($B$2:$B$12>0)))
D3	D3	=SUMPRODUCT((--($A$2:$A$12>=$F$5))*(--($A$2:$A$12<(1+EOMONTH($F$5,0))))*(B2:$B$12=""))

 

[AYSHANA]

still, maybe because my excel is 2013 version?

Book1.xlsx
	A	B	C	D	E	F
1	UNIT ALLOCATED IN SYSTEM	UNIT ISSUED IN SYSTEM
2	15-02-23 9:50		WITHOUT BLANK	WITH BLANK
3	15-02-23 14:44		6	3
4	15-02-23 14:44	15-02-23 15:41
5	14-02-23 17:38	14-02-23 18:16				01-02-23 0:00
6	16-02-23 10:56	16-02-23 11:00
7	15-02-23 23:51	16-02-23 0:02
8	17-02-23 8:58	17-02-23 9:06
9	19-02-23 9:40	19-02-23 13:24
10	19-02-23 9:40
11	19-02-23 5:56
12	18-02-23 19:44
Sheet1

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT((--($A$2:$A$12>=$F$5))*(--($A$2:$A$12<(1+EOMONTH($F$5,0))))*(--(B2:B12>0)))
D3	D3	=SUMPRODUCT((--($A$2:$A$12>=$F$5))*(--($A$2:$A$12<(1+EOMONTH($F$5,0))))*(B2:$B$12=""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[awoohaw]

i think you have non printing values in cells B11 and B12. When I hover over them on this webpage a zero pops up.