# Need help on count formula within the cell.

1. ## Need help on count formula within the cell.

Hi,

Need help on count formula within the cell.

I want the count of July and August number of days excluding commas .

Sample excel file attached.

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

Thanks.

2. ## Re: Need help on count formula within the cell.

Are you still using Excel 2016?

3. ## Re: Need help on count formula within the cell.

Hi,

No Ma'am. I'm using MS 2019.

4. ## Re: Need help on count formula within the cell.

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!

5. ## Re: Need help on count formula within the cell.

For August:

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

6. ## Re: Need help on count formula within the cell.

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

7. ## Re: Need help on count formula within the cell.

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.

8. ## Re: Need help on count formula within the cell.

Hi,

For July the result is correct, but for Aug showing wrong.

9. ## Re: Need help on count formula within the cell.

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

10. ## Re: Need help on count formula within the cell.

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.

11. ## Re: Need help on count formula within the cell.

No sir that's typo error.

And post #9?

13. ## Re: Need help on count formula within the cell.

I think Post 7 is correct...

14. ## Re: Need help on count formula within the cell.

@mrrobottelg

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

15. ## Re: Need help on count formula within the cell.

results showing correct.

thanks for the formula.

16. ## Re: Need help on count formula within the cell.

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.

17. ## Re: Need help on count formula within the cell.

Yours

Post 7 really help thanks a lot.

18. ## Re: Need help on count formula within the cell.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1