Formula to Count the Number of Dates Entered

[Dave33]

My wife has a part time job at a riding stable and one of her duties is sending out monthly invoices to customers. The stable uses Excel for billing and there are over 50 invoices for various services she has to create. I'm trying to help her streamline this process as it takes her a long time to get them done. She's an Excel novice. I did help by setting up some.


One of the services the stable provides is horse exercising and turn outs. She has to list all the dates this service was provided for the month on each invoice then count the number of dates and insert this number in cell D22. In the example sheet I attached I inserted 4 dates in B22. 12, 13, 14, 15 which totals 4 in D22. But when you remove the dates, it still populates a 1 and places a charge in cell E22. What I need is cell B22 when blank, not to populate a number and corresponding charge.

Thanks in advance.

Book10.xlsx
	A	B	C	D	E
1	Stable Name
2	Address
3	Address 2
4
5	Date: October 2022
6
7
8	Horse Owner Name
9	Address
10	Address 2
11
12	Horse Name:
13
14	Previous Balance:
15	Payments Received:
16	Balance carried forward:				$ -
17
18	Board				$ 100.00
19	NYS 7% Tax				$ 7.00
20
21	Turnout, Lunge & Walks
22	Date/s	12, 13, 14, 15	$ 6.00	4	$ 24.00
23					$ -
24	Staff Rides
25	Date/s		$ 20.00	1	$ 20.00
26
27	Lessons
28	Date/s (Semi Private)		$ 40.00	1	$ 40.00
29
30	Date/s (Private)		$ 45.00	1	$ 45.00
31
32	Special Care
33
34	Current Statement Charges				$ 236.00
35
36	Total Due				$ 236.00
Board Lease Template

Cell Formulas
Range	Formula
E16	E16	=E14+E15
E19	E19	=18:18*0.07
D22,D30,D28,D25	D22	=LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1
E30,E28,E25,E22:E23	E22	=C22*D22
E34	E34	=SUM(E18:E33)
E36	E36	=E16+E34

 

[Dave Patton]

With earlier versions of Excel, try

----

 

[Dave Patton]

My apologizes; I used old school arithmetic and 4 + 3 = 7

With older versions of Excel, you can use SumProduct.

T202211_.xlsm
	B	C					H		J
21
22	12, 13, 14, 15						7		7
23	16, 17, 20
24
25	12						1		1
26
5c

Cell Formulas
Range	Formula
H22,H25	H22	=(B22<>"")*(LEN(B22)-LEN(SUBSTITUTE(B22,",",""))+1)+(B23<>"")*(LEN(B23)-LEN(SUBSTITUTE(B23,",",""))+1)
J22,J25	J22	=SUMPRODUCT(LEN(B22:B23)-LEN(SUBSTITUTE(B22:B23,",",""))+(--(LEN(B22:B23)>0)))

Named Ranges
Name	Refers To	Cells
rN	='5c'!$B$22:$B$23	J22, H22