Need help on count formula within the cell.

Are you still using Excel 2016?

OK - please update your forum profile.

For July:

=LEN(LEFT(A3,FIND(PROPER(B2),A3)))-LEN(SUBSTITUTE(LEFT(A3,FIND(PROPER(B2),A3)),",",""))+1

Just looking at August. Would be much easier with 365!

For August:

=LEN(MID(A3,FIND(PROPER(B2),A3),99))-LEN(SUBSTITUTE(MID(A3,FIND(PROPER(B2),A3),99),",",""))+1

Any more variations ... with double commas ",," and missing commas (1218) ?

1,2,4,6,,25,27,28 July 7,8,9,10,11,1218,22,2326,28,29 Aug

For A3 July

Formula:

Please Login or Register  to view this content.

but will not work for A4

Are the double commas and missing commas intentional, or typos. If typos, one formula copied across and down:

=LEN(FILTERXML("<A><B>"&SUBSTITUTE(LOWER($A3)," ","</B><B>")&"</B></A>","//B[following::*[1]='"&B$2&"']"))-LEN(SUBSTITUTE(FILTERXML("<A><B>"&SUBSTITUTE(LOWER($A3)," ","</B><B>")&"</B></A>","//B[following::*[1]='"&B$2&"']"),",",""))+1

In your Excel product, this will be an array formula and will need set using CTRL-SHIFT-ENTER instead of enter, before dragging across/down.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E
1		result
2	data	july	aug
3	1,2,4,6,7,8,9,22,24,25,27,28 July 7,8,9,10,11,12,14,15,17,18,22,23,24,25,26,28,29 Aug	12	17	C3:	=LEN(MID(A3,FIND(PROPER(B2),A3),99))-LEN(SUBSTITUTE(MID(A3,FIND(PROPER(B2),A3),99),",",""))+1
4	1,2,4,6,,25,27,28 July 7,8,9,10,11,1218,22,2326,28,29 Aug	13	5	C4:	=LEN(MID(A4,FIND(PROPER(B3),A4),99))-LEN(SUBSTITUTE(MID(A4,FIND(PROPER(B3),A4),99),",",""))+1

Sheet: Sheet3

If the missing commas are intended to be there, as in 2326 of August... this is impossible, as Excel would not be able to distinguish 12 from 1,2 with a missing comma.

And post #9?

I think Post 7 is correct...

@mrrobottelg

Could you please provide feedback on both post #7 and post #9?

Who are you talking to?

In any event, you're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Thanks for that,. It's always helpful for us to know what worked, so we can learn from the experience as well.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.