+ Reply to Thread
Results 1 to 18 of 18

Countif - only up to where the required text changes?

  1. #1
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Post Countif - only up to where the required text changes?

    Hi
    I wonder if anyone can help, or if this is even possible?!

    I want to use the COUNTIF function to count the number of cells containing a certain TEXT. However, if the text value then changes, I want the count to stop, even if the same text value appears again.

    For example, let's say I have 5 columns containing the word "HELLO", then 1 column which contains the word "GOODBYE", and then another column which contains "HELLO" again. Normally, across the whole range of columns, the value would be 6. However, I want to return 5.

    Is this possible?!

    Best wishes.

  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: Countif - only up to where the required text changes?

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    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
    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: Countif - only up to where the required text changes?

    With your entries within the range A1:I1:

    =COUNTA(TAKE(A1:I1,,LOOKUP(2,1/((A1:I1<>"HELLO")*(A1:I1<>"")),COLUMN(A1:I1))-1))

    Change the range to suit.
    Last edited by AliGW; 04-18-2024 at 04:37 AM. Reason: Typo fixed.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,504

    Re: Countif - only up to where the required text changes?

    I understand his point differently:
    After counting the text for the first time as 5, he then wants to hardcore this result (paste value).
    If that's correct, then I believe VBA code is the only solution.
    Last edited by AliGW; 04-18-2024 at 05:07 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.
    Quang PT

  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: Countif - only up to where the required text changes?

    We shall have to wait for the OP to confirm, but if they are using 365 online (in a browser), VBA will not be an option.

  6. #6
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Re: Countif - only up to where the required text changes?

    Many thanks for this help.

    I have tried it, but it doesn't seem to be giving the correct result.

    Here is my actual code:

    =COUNTA(TAKE('Event 1'!$D$5:$AG$5,,LOOKUP(2,1/(('Event 1'!$D$5:$AG$5<>C109)*('Event 1'!$D$5:$AG$5<>"")),COLUMN('Event 1'!$D$5:$AG$5))-1))

    In the table, C109 is the name I am trying to look up.

    I have just tried using a column with name "TX", then "TRAVEL" and then "TX" again. The result should be 1, but I am getting 3.

  7. #7
    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: Countif - only up to where the required text changes?

    Please attach a sample workbook (instructions in the yellow banner at the top).

    I'll bet that your example was over-simplified.

  8. #8
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Re: Countif - only up to where the required text changes?

    Thank you!

    I will do.

  9. #9
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Re: Countif - only up to where the required text changes?

    I have uploaded a sample file.

    The columns I wish to count are shown in the EVENT tab.

    The table I want to pull the values through to are in the DATA tab.

    So each row of the table needs to show the count values until the text changes, but then add another count row if the same text appears again.
    Attached Files Attached Files

  10. #10
    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: Countif - only up to where the required text changes?

    So each row of the table needs to show the count values until the text changes, but then add another count row if the same text appears again.
    This seems to be different to what you said in your opening post. Have you included EXPECTED results?

    So what RESULT are you expecting? And why?

  11. #11
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Re: Countif - only up to where the required text changes?

    So in the example, the DATA table should show:

    3 TRAVEL
    1 REST
    1 TRAVEL

  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: Countif - only up to where the required text changes?

    That's completely different!

    Explain WHY you need these counts - what's the ultimate aim here?

    What is the CONTEXT? I suspect that you are trying to so something and over-complicating it. Give us the whole picture.
    Last edited by AliGW; 04-18-2024 at 06:07 AM.

  13. #13
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Re: Countif - only up to where the required text changes?

    OK. I am sorry!

    The DATA table is used to import into a PowerApp via Powerautomate, which is working fine.

    The event list is a column list of different day types from a starting date. So let's say the job starts on April 1st, and the column has TX, TX, TX, Travel, TX. The DATA table needs to show 3 TX, then a row with 1 Travel and then another tow which shows 1 TX again.

  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: Countif - only up to where the required text changes?

    So is this a sort of GANTT chart? And if so, are the columns dated?

  15. #15
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Re: Countif - only up to where the required text changes?

    Yes. The date in which they are added to Gantt chart in my PowerApps relate to the start date of the job, then the number of days from the start date for each row in the table (DAYS_FROM_START) and then the duration of the Gantt row is determined by the DAYS_FOR_CALENDAR column.

  16. #16
    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: Countif - only up to where the required text changes?

    So are you attempting to block days on a calendar to match the GANTT chart? If so, then I would just use a lookup formula.

    I'm still not clear on why you need these counts and what they will be used for.

  17. #17
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Re: Countif - only up to where the required text changes?

    To get the data into my SP List for PowerApps to use, I need a record for each day type which I am taking from the DATA table in the Excel file.

    It works perfectly when each day type has a separate name, but when it repeats, with a gap in between, that's when I get the error. If I had unique names for each day type, the countif function works fine. I need the countif to determine how many occurances there are for each day type which then feeds into the the DAYS_FOR_CALENDAR column.

  18. #18
    Registered User
    Join Date
    04-18-2024
    Location
    Nottingham
    MS-Off Ver
    365
    Posts
    9

    Re: Countif - only up to where the required text changes?

    I use a PowerAutomate script to pull each line from the Excel DATA table, using an ODATA filter to only pull the lines when the DAYS_FOR_CALENDAR is greater than 0.

+ 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. Possible COUNTIF help required
    By Paul JS in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2021, 03:04 PM
  2. [SOLVED] Countif formula required with various criteria
    By reddaze in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-24-2017, 09:54 AM
  3. [SOLVED] Help required with COUNTIF function
    By RossKennedy in forum Excel General
    Replies: 5
    Last Post: 09-19-2016, 12:53 PM
  4. [SOLVED] Simple Conditional Formating Help Required - If Blank then Red Bold text required
    By thilag in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-19-2014, 12:23 PM
  5. [SOLVED] Countif Help required
    By thilag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-06-2014, 12:34 AM
  6. [SOLVED] COUNTIF or COUNT formula help required
    By MYEM1983 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-05-2014, 01:42 PM
  7. [SOLVED] Help required with COUNTIF/AND function
    By DAT in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2006, 02:35 PM

Tags for this Thread

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