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.

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.

Thank you!

I will do.

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.

So in the example, the DATA table should show:

3 TRAVEL
1 REST
1 TRAVEL

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.

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.

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.

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.