Formula for calculating count based on date or month ranges

[Pancakey]

Hello,

I am a beginner using Excel formulas and I am trying to count the number of cells (column C of the first image) that falls within each month of the year (column I, see the second image).

I know the formula below is incorrect, and I am trying to figure out how the date range for each month can fall within it with a number count as the result.
=COUNTIFS('Purchase Order'!C:C,">=6/31/2023",'Purchase Order'!C:C,"<=6/1/2023")

For example, C4:C6 in the first screenshot has the dates 11/28/23, so I want a formula in column I, PO Month, see the second screenshot, to identify these date ranges within the month of November and so forth.

Additionally, is there anything different for the formula if I want to count the dollar value of a column that falls under a specific month for POs that has been issued?

 

[kevin9999]

Demo of one way:

Book1
	B	C	D	E	F	G	H	I	J	K
3	#	date	$					PO Month	#	$
4	1	11/28/23	$10.00					September 23	0	$0.00
5	2	11/28/23	$10.00					October 23	0	$0.00
6	3	11/28/23	$10.00					November 23	5	$50.00
7	4	11/28/23	$10.00					December 23	4	$40.00
8	5	11/28/23	$10.00					January 24	0	$0.00
9	6	12/1/23	$10.00
10	7	12/1/23	$10.00
11	8	12/1/23	$10.00
12	9	12/1/23	$10.00
Sheet2

Cell Formulas
Range	Formula
J4:J8	J4	=SUMPRODUCT(--(TEXT($C$4:$C$12,"mmmm yy")=TEXT(I4,"mmmm yy")))
K4:K8	K4	=SUMPRODUCT(--(TEXT($C$4:$C$12,"mmmm yy")=TEXT(I4,"mmmm yy")),$D$4:$D$12)

 

[Pancakey]

The formula seems to work, however it doesn't seem to work on my table for some reason.

In your example:
Column C, I have a formula in the cell. See below sample. I am not sure if this is affecting the sumproduct formula.
=VLOOKUP(E7,'PO Line Data UPD 11-30-23'!$1:$1048576,5,FALSE)

On a separate note, if I wanted to find out the total amount paid vs unpaid in one separate cell each (paid and unpaid) based on the total POs issued or cells entered, how would I do so?

See above image between columns K to M. Would we use a sumif formula?

 

[Pancakey]

I forgot to mention in your column I, I have the below formula entered. I am wondering if this formula is also affecting the sum formula.

=DATE(YEAR(StartDate),MONTH(StartDate),1)

 

[kevin9999]

it doesn't seem to work on my table for some reason.

Could you post your table using the XL2BB add in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform?

 

[Pancakey]

Hello, I have three tabs in my worksheet, please see below for the first tab.

FIRST TAB: DASHBOARD
Note: Currently working on the formulas for the tables...the information I want to populate are on this tab.

PO Status Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
4
5
6		START DATE				END DATE
7		Thursday, June 1, 2023				Tuesday, December 31, 2024
8
9		TOTAL PO ISSUED
10
11		97	-
12
13
14		TOTAL PO PAID
15
16		97	3,764.5																				ISSUED (97)	PAID (97) - 0.0%
17																						PO #	97	97	0.0%
18
19		TOTAL PO UNPAID																					ISSUED	PAID - 0.0%
20																						PO ($)	#REF!	3,764	0.0%
21		0	(3,764.5)
22						STATUS	#		PO	ISSUED		PAID		%		AVE PAID DAYS							Issued (Amount)	Paid (Amount)	Paid Trend (Days)
23						Sent	88		MONTH	#	AMOUNT	#	AMOUNT	#	AMOUNT							Jun 23	#REF!	-	#REF!
24						Not Sent	8		June 23	0	#REF!	0	-	0.00%	0.00%	#REF!						Jul 23	#REF!	616.7	#REF!
25						Pending Payment	62		July 23	6	#REF!	6	616.7	100.00%	0.00%	#REF!						Aug 23	#REF!	627.7	#REF!
26						Partially Paid			August 23	9	#REF!	9	627.7	100.00%	0.00%	#REF!						Sep 23	#REF!	2,520.1	#REF!
27						Paid	35		September 23	8	#REF!	8	2,520.1	100.00%	0.00%	#REF!						Oct 23	#REF!	-	#REF!
28						Completed	35		October 23	0	#REF!	0	-	0.00%	0.00%	#REF!						Nov 23	#REF!	-	#REF!
29						Cancelled	1		November 23	74	#REF!	0	-	0.00%	0.00%	#REF!						Dec 23	#REF!	-	#REF!
30						TOTAL	97		December 23	0	#REF!	0	-	0.00%	0.00%	#REF!						Jan 24	#REF!	-	#REF!
31									January 24	0	#REF!	0	-	0.00%	0.00%	#REF!						Feb 24	#REF!	-	#REF!
32									February 24	0	#REF!	0	-	0.00%	0.00%	#REF!						Mar 24	#REF!	-	#REF!
33									March 24	0	#REF!	0	-	0.00%	0.00%	#REF!						Apr 24	#REF!	-	#REF!
34									April 24	0	#REF!	0	-	0.00%	0.00%	#REF!						May 24	#REF!	-	#REF!
35									May 24	0	#REF!	0	-	0.00%	0.00%	#REF!
Dashboard

Cell Formulas
Range	Formula
B11	B11	=W17
C11	C11	=W4
B16	B16	=X17
C16	C16	=X20
W16	W16	="ISSUED ("&W17&")"
X16	X16	="PAID ("&X17&") - "&TEXT(Y17,"0.0%")
W17	W17	=SUM(J24:J35)
X17	X17	=SUM(J24:J35)
Y17	Y17	=IFERROR(#REF!/#REF!,0)
X19	X19	="PAID"&" - "&TEXT(Y20,"0.0%")
W20	W20	=SUM(K24:K35)
X20	X20	=SUM(M24:M35)
Y20	Y20	=IFERROR(X20/W20,0)
B21:C21	B21	=B11-B16
V23:V34	V23	=I24
W23:W34	W23	=K24
X23:X34	X23	=M24
Y23:Y34	Y23	=P24
I24	I24	=DATE(YEAR(StartDate),MONTH(StartDate),1)
J24:J35	J24	=SUMPRODUCT(--(TEXT('Purchase Order'!$C:$C,"mmmm yy")=TEXT(I24,"mmmm yy")))
K24:K35	K24	=SUMPRODUCT(--(TEXT('Purchase Order'!$C:$C,"mmmm yy")=TEXT(I24,"mmmm yy")),'Purchase Order'!$F:$F)
L24:L35	L24	=COUNTIFS('Purchase Order'!R:R,">=0",'Purchase Order'!C:C,">="&I24,'Purchase Order'!C:C,"<="&EOMONTH(I24,0))
M24:M35	M24	=SUMIFS('Purchase Order'!R:R,'Purchase Order'!C:C,">="&I24,'Purchase Order'!C:C,"<="&EOMONTH(I24,0))
N24:O35	N24	=IFERROR(L24/J24,0)
P24:P35	P24	=IF(K24<>0,IFERROR(SUMIFS('Purchase Order'!#REF!,'Purchase Order'!#REF!,">=0",'Purchase Order'!C:C,">="&I24,'Purchase Order'!C:C,"<="&EOMONTH(I24,0))/L24,0),NA())
I25:I35	I25	=EOMONTH(I24,0)+1
G23	G23	=SUM(COUNTIF('Purchase Order'!I:I,"Issued"))
G24	G24	=SUM(COUNTIF('Purchase Order'!I:I,"Not Sent"))
G25	G25	=SUM(COUNTIF('Purchase Order'!Q:Q,"Unpaid"))
G27	G27	=SUM(COUNTIF('Purchase Order'!Q:Q,"Paid"))
G28	G28	=SUM(COUNTIF('Purchase Order'!K:K,"Completed"))
G29	G29	=SUM(COUNTIF('Purchase Order'!I:I,"Cancelled"))
G30	G30	=SUM(G23:G24,G29)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P24:P35	Expression	=ISNA(P24)	text	NO

 

[Pancakey]

My data is too long, so I shorten this list to 20 lines of data.

SECOND TAB: PURCHASE ORDER

PO Status Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		No	PO Date	PO #	Supplier Name	Purchase Order Total	Requester Name	PO Transmission Status	PO Issued	PO Acknowledged	PO Status	Delivered Date	Order Notes	Invoiced	Net Terms	If Paid (T/F)	Payment Status	Total Invoiced
3
4		1	11/30/23	5096	Target	35.96	Claire	Awaiting Online Purchase	Not Sent			-		No	Prepayment	FALSE	Unpaid	-
5		2	11/30/23	5095	Home Depot	35.96	Ken	Awaiting Online Purchase	Not Sent			-		No	Prepayment	FALSE	Unpaid	-
6		3	11/30/23	5094	Swagelok	35.96	Matthew	Awaiting Online Purchase	Not Sent			-		No	Net 30	FALSE	Unpaid	-
7		4	11/30/23	5093	Sigma-Aldrich	35.96	Anna	Awaiting Online Purchase	Not Sent			-		No	Net 30	FALSE	Unpaid	-
8		5	11/30/23	5092	Target	59.97	Claire	Sent via cxml	Issued			-		No	Prepayment	FALSE	Unpaid	-
9		6	11/30/23	5091	Home Depot	59.97	Ken	Sent via cxml	Issued			-		No	Prepayment	FALSE	Unpaid	-
10		7	11/30/23	5090	Swagelok	12.05	Matthew	Sent via cxml	Issued			-		No	Net 30	FALSE	Unpaid	-
11		8	11/30/23	5089	Sigma-Aldrich	8.99	Anna	Sent via cxml	Issued			-		No	Net 30	FALSE	Unpaid	-
12		9	11/30/23	5088	Target	170.35	Claire	Sent Manually	Issued			-		No	Prepayment	FALSE	Unpaid	-
13		10	11/30/23	5087	Home Depot	48.28	Ken	Sent via cxml	Issued			-		No	Prepayment	FALSE	Unpaid	-
14		11	11/30/23	5086	Swagelok	48.28	Matthew	Sent via cxml	Issued			-		No	Net 30	FALSE	Unpaid	-
15		12	11/29/23	5085	Sigma-Aldrich	177.99	Anna	Sent Manually	Issued			-		No	Net 30	FALSE	Unpaid	-
16		13	11/29/23	5084	Target	1800.00	Claire	Pending Manual	Not Sent			-		No	Prepayment	FALSE	Unpaid	-
17		14	11/29/23	5083	Home Depot	808.00	Ken	Pending Manual	Not Sent			-		No	Prepayment	FALSE	Unpaid	-
18		15	11/29/23	5082	Swagelok	26.97	Matthew	Awaiting Online Purchase	Not Sent			-		No	Net 30	FALSE	Unpaid	-
19		16	11/29/23	5081	Sigma-Aldrich	108.00	Anna	Sent via cxml	Issued			-		No	Net 30	FALSE	Unpaid	-
20		17	11/29/23	5080	Target	21.99	Claire	Sent via cxml	Issued			-		No	Prepayment	FALSE	Unpaid	-
21		18	11/29/23	5079	Home Depot	47.86	Ken	Sent via cxml	Issued			-		No	Prepayment	FALSE	Unpaid	-
22		19	11/29/23	5078	Swagelok	141.35	Matthew	Sent Manually	Issued			-		No	Net 30	FALSE	Unpaid	-
23		20	11/29/23	5077	Sigma-Aldrich	141.35	Anna	Sent Manually	Issued			-		No	Net 30	FALSE	Unpaid	-
Purchase Order

Cell Formulas
Range	Formula
H4:H23	H4	=IFNA(VLOOKUP(D4,'PO Line Data UPD 11-28-23'!$1:$1048576,3,FALSE),"")
I4	I4	=IF(OR(H4="Sent via cxml",H4="Sent via Email",H4="Sent Manually"),"Sent",IF(OR(H4="Not Sent",H4="Awaiting Online Purchase",H4="Pending Manual",H4=""),"Not Sent",(IF(H4="Pending Manual Cancel","Cancelled",""))))
I5:I23	I5	=IF(OR(H5="Sent via cxml",H5="Sent via Email",H5="Sent Manually"),"Issued",IF(OR(H5="Not Sent",H5="Awaiting Online Purchase",H5="Pending Manual",H5=""),"Not Sent",(IF(H5="Pending Manual Cancel","Cancelled",""))))
K4:K23	K4	=IF(AND(L4<>"",Q4="Paid"),"Completed","")
L4:L23	L4	=IFNA(VLOOKUP(D4,'PO Line Data UPD 11-28-23'!$1:$1048576,11,FALSE),"")
N4:N23	N4	=IFNA(IF(R4="*","Yes","No"),"")
O4:O23	O4	=IFNA(VLOOKUP(D4,'PO Line Data UPD 11-28-23'!$1:$1048576,9,FALSE),"")
P4:P23	P4	=IFNA(VLOOKUP(D4,'PO Line Data UPD 11-28-23'!$1:$1048576,12,FALSE),0)
Q4:Q23	Q4	=IF(P4=TRUE,"Paid","Unpaid")
R4:R23	R4	=IFNA(VLOOKUP(D4,'PO Line Data UPD 11-28-23'!$1:$1048576,8,FALSE),"")
A4:A23	A4	=IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,IF(#REF!<>"",#REF!,0))))))))))
C4:C23	C4	=VLOOKUP(D4,'PO Line Data UPD 11-28-23'!$1:$1048576,2,FALSE)
F4:F23	F4	=VLOOKUP(D4,'PO Line Data UPD 11-28-23'!$1:$1048576,7,FALSE)

Named Ranges
Name	Refers To	Cells
InvoiceList	='Purchase Order'!$D$4:$D$5003	R4, O4:P4, L4, H4, F4, C4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K4:K4001	Cell Value	contains "Completed"	text	NO
R4:R4001	Cell Value	contains "Not Sent"	text	NO
R4:R4001	Cell Value	contains "Awaiting Online Purchase"	text	NO
R4:R4001	Cell Value	contains "Pending Manual"	text	NO
R4:R4001	Cell	contains a blank value	text	NO
H4:H5003	Cell Value	contains "Not Sent"	text	NO
H4:H5003	Cell Value	contains "Awaiting Online Purchase"	text	NO
H4:H5003	Cell Value	contains "Pending Manual"	text	NO
J4:J5003	Cell Value	contains "No"	text	NO
J4:J5003	Cell Value	contains "Yes"	text	NO
P4:R4,P5:Q5003,R5:R4001,C4:O5003	Expression	=AND(#REF!<TODAY(),#REF!="Unpaid")	text	NO
H4:H5003	Cell	contains a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J4:J23	List	Yes, No
M4:M23	List	Awaiting Response from Supplier, Order Hold - Pending Payment, Followed-up with the Supplier, Order Lost/Missing, Payment Issue, PO Hold, Write Off

 

[Pancakey]

I am not sure if this is helpful, but this is the third sample data tab.

THIRD TAB: PO DATA

PO Status Tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	PO Number	Order Date	Order Transmission Status (Header)	Supplier	Item	Order Status (Header)	Order Total	Total Invoiced	Payment Term	Requested By (Header)	Received Date	Paid
2	5096	11/30/23	Awaiting Online Purchase	Target	Sample Item	Issued	35.96		Prepayment	Claire		FALSE
3	5095	11/30/23	Awaiting Online Purchase	Home Depot	Sample Item	Issued	35.96		Prepayment	Ken		FALSE
4	5094	11/30/23	Awaiting Online Purchase	Swagelok	Sample Item	Issued	35.96		Net 30	Matthew		FALSE
5	5093	11/30/23	Awaiting Online Purchase	Sigma-Aldrich	Sample Item	Issued	35.96		Net 30	Anna		FALSE
6	5092	11/30/23	Sent via cxml	Target	Sample Item	Issued	59.97		Prepayment	Claire		FALSE
7	5091	11/30/23	Sent via cxml	Home Depot	Sample Item	Issued	59.97		Prepayment	Ken		FALSE
8	5090	11/30/23	Sent via cxml	Swagelok	Sample Item	Issued	12.05		Net 30	Matthew		FALSE
9	5089	11/30/23	Sent via cxml	Sigma-Aldrich	Sample Item	Issued	8.99		Net 30	Anna		FALSE
10	5088	11/30/23	Sent Manually	Target	Sample Item	Issued	170.35		Prepayment	Claire		FALSE
11	5087	11/30/23	Sent via cxml	Home Depot	Sample Item	Issued	48.28		Prepayment	Ken		FALSE
12	5086	11/30/23	Sent via cxml	Swagelok	Sample Item	Issued	48.28		Net 30	Matthew		FALSE
13	5085	11/29/23	Sent Manually	Sigma-Aldrich	Sample Item	Issued	177.99		Net 30	Anna		FALSE
14	5084	11/29/23	Pending Manual	Target	Sample Item	Issued	1800.00		Prepayment	Claire		FALSE
15	5083	11/29/23	Pending Manual	Home Depot	Sample Item	Issued	808.00		Prepayment	Ken		FALSE
16	5082	11/29/23	Awaiting Online Purchase	Swagelok	Sample Item	Issued	26.97		Net 30	Matthew		FALSE
17	5081	11/29/23	Sent via cxml	Sigma-Aldrich	Sample Item	Issued	108.00		Net 30	Anna		FALSE
18	5080	11/29/23	Sent via cxml	Target	Sample Item	Issued	21.99		Prepayment	Claire		FALSE
19	5079	11/29/23	Sent via cxml	Home Depot	Sample Item	Issued	47.86		Prepayment	Ken		FALSE
20	5078	11/29/23	Sent Manually	Swagelok	Sample Item	Issued	141.35		Net 30	Matthew		FALSE
PO Line Data UPD 11-28-23

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J100	Expression	=AND(#REF!<TODAY(),#REF!="Unpaid")	text	NO
D2:D100	Expression	=AND(#REF!<TODAY(),#REF!="Unpaid")	text	NO
A2:A98	Expression	=AND(#REF!<TODAY(),#REF!="Unpaid")	text	NO