Formula not working to show total sales per day, #VALUE! Can't find the issue (Ignore Conditionals) I3 works fine, J3&J4 are what I have tried.

[CorbenW01]

Testsheet.xlsx
	B	C	D	E	F	G	H	I	J
2	Date	Job Reference	Initial Quote Total	W/L	Final Invoice Total			# of Leads per date	Sales Per Date
3							1/05/2024	0	0
4	2/05/2024	Wang	$3,964.31	W	$3,964.31		2/05/2024	1	#VALUE!
5	10/05/2024	Pethig	$3,790.40	L			3/05/2024	0	0
6	11/05/2024	Wylde	$5,721.31	W	$5,304.92		4/05/2024	0	0
7	13/05/2024	Young	$1,185.00	W	$1,185.00		5/05/2024	0	0
8	14/05/2024	O'brien	$3,255.53	W	$3,255.53		6/05/2024	0	0
9	15/05/2024	Parker	$1,273.15	W	$1,273.15		7/05/2024	0	0
10	16/05/2024	Theobald	$9,880.00	W	$8,707.69		8/05/2024	0	0
11	17/05/2024	Rodwell	$3,975.84				9/05/2024	0	0
Sheet1

Cell Formulas
Range	Formula
I3:I11	I3	=COUNTIF(B:B,H3)
J3,J5:J11	J3	=COUNTIFS($B$4:$B$100,"="&H3,$B$4:$B$100,">"&H3,$E$4:$E$100,L3)
J4	J4	=SUM(COUNTIFS($B$4:$B$101,">="&DATE(2024,5,1),$B$4:$B$101,"<="&DATE(2024,5,2),E4:E100,"W"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:F57,F62:F105	Expression	=$P4="Declined"	text	NO
F4:F57,F62:F105	Expression	=$P4="Completed - Account"	text	NO
F4:F57,F62:F105	Expression	=$P4="Complete"	text	NO
E4:E62	Cell Value	contains "L"	text	NO
E4:E62	Cell Value	contains "W"	text	NO
C4:D57,B4:B105,C59:C105,D62:D105	Expression	=$R4="Completed - Account"	text	NO
C4:D57,B4:B105,C59:C105,D62:D105	Expression	=$R4="Complete"	text	NO
C4:D57,B4:B105,C58:C105,D62:D105	Expression	=$R4="Declined"	text	NO
C4:C105	Expression	=$R4="TO DO"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:C3	Any value
E4:E11	List	W, L

 

[Asbestos_Jen]

Glad to have helped, and thanks for the feedback!

 

[CorbenW01]

Hi Last Issue i can't seem to fix. the 10/05/2024 for this range has 1 lead, and 1 won sale. however (using your formula from earlier) it still shows as 0 sales per date. Thoughts?

Testsheet.xlsx
	O	P	Q	R	S
5	10/05/2024	Matt H	$6,133.00	W	$6,911.00
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S4:T48,S50:T196	Expression	=$L4="Declined"	text	NO
S4:T48,S50:T196	Expression	=$L4="Completed - Account"	text	NO
S4:T48,S50:T196	Expression	=$L4="Complete"	text	NO
R4:R196	Cell Value	contains "L"	text	NO
R4:R196	Cell Value	contains "W"	text	NO
O4:Q48,P50:P51,Q50:Q105,O51:O105,P53:P58,P60:P62,P64:P72,P74:P105	Expression	=$V4="Declined"	text	NO
O4:Q48,P50:P51,Q50:Q105,O51:O105,P53:P58,P60:P62,P64:P72,P74:P105	Expression	=$V4="Completed - Account"	text	NO
O4:Q48,P50:P51,Q50:Q105,O51:O105,P53:P58,P60:P62,P64:P72,P74:P105	Expression	=$V4="Complete"	text	NO

Testsheet.xlsx
	U	V	W	X
12	10/05/2024	1	0	$6,911.00
Sheet1

Cell Formulas
Range	Formula
V12	V12	=COUNTIF(O:O,U12)
W12	W12	=COUNTIFS($O$4:$O$101,"="&U12,$R$3:$R$100,"W")
X12	X12	=SUMIFS($S$4:$S$101,$O$4:$O$101,U12,$R$4:$R$101,"W")

 

[Alex Blakenburg]

You're 2 ranges in the Count formula don't line up.
Your sum consistently uses the range rows 4 to 101 but your count has 4-101 and 3-100.

 

[Asbestos_Jen]

You're 2 ranges in the Count formula don't line up.
Your sum consistently uses the range rows 4 to 101 but your count has 4-101 and 3-100.

That's exactly what I came here to say. It's probably messed up other things in the column, too.

 

[CorbenW01]

oh right, so they all have to match is what you guys are saying?

 

[Asbestos_Jen]

oh right, so they all have to match is what you guys are saying?

Yes, the ranges need to match.