+ Reply to Thread
Results 1 to 18 of 18

Need help on count formula within the cell.

  1. #1
    Registered User
    Join Date
    03-30-2024
    Location
    Hyderabad
    MS-Off Ver
    Office 365
    Posts
    17

    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



    Please help.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Need help on count formula within the cell.

    Are you still using Excel 2016?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    03-30-2024
    Location
    Hyderabad
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Need help on count formula within the cell.

    Hi,

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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Need help on count formula within the cell.

    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!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    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. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,848

    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: copy to clipboard
    Please Login or Register  to view this content.


    but will not work for A4
    Last edited by JohnTopley; 06-13-2024 at 07:23 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    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.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  8. #8
    Registered User
    Join Date
    03-30-2024
    Location
    Hyderabad
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Need help on count formula within the cell.

    Hi,

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

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    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
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    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. #11
    Registered User
    Join Date
    03-30-2024
    Location
    Hyderabad
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Need help on count formula within the cell.

    No sir that's typo error.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Need help on count formula within the cell.

    And post #9?

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Need help on count formula within the cell.

    I think Post 7 is correct...

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Need help on count formula within the cell.

    @mrrobottelg

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

  15. #15
    Registered User
    Join Date
    03-30-2024
    Location
    Hyderabad
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Need help on count formula within the cell.

    results showing correct.


    thanks for the formula.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    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.

    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.

  17. #17
    Registered User
    Join Date
    03-30-2024
    Location
    Hyderabad
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Need help on count formula within the cell.

    Yours

    Post 7 really help thanks a lot.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    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.

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] how to get count from formula using cell
    By emmr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-20-2022, 03:47 AM
  2. [SOLVED] Can a cell formula count only the cell values that are in bold?
    By chrisneu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-26-2021, 09:35 PM
  3. Formula needed to count cell if matching criteria in another cell
    By ibleedorange14 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-03-2017, 12:37 PM
  4. Formula to count blank cell
    By syed82 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-16-2016, 02:26 AM
  5. formula help - conditional format to colour cell = count = formula
    By rangeruk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2015, 09:03 PM
  6. Excel 2007 : formula in cell to sum and count
    By mevikasjain in forum Excel General
    Replies: 2
    Last Post: 06-26-2011, 10:01 AM
  7. Count day only if cell has a value formula
    By pullnshoot25 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-15-2007, 01:45 AM

Bookmarks

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