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

1. ## 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. ## 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.

3. ## 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.

4. ## 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.

5. ## 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. ## 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. ## 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.

Thank you!

I will do.

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.

10. ## 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. ## 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. ## 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.

13. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

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