Counting series' of text within a row

[Kipper63]

Hi, I have a table with rows of data where the column headings are dates and a code 'd' is added for particular instances of activity on that date.
I have included a simple example below.
I am trying to calculate how many series of 'd' there are i.e. there are 4 unique series of 'd' code appearing. I am not interested in the number of 'd's.
I am struggling to get started, I am hoping someone can help?

A B C D E F G H I J K L M N O P Q R
Row 1 01/03/20 02/03/20 03/03/20 04/03/20 05/03/20 06/03/20 07/03/20 08/03/20 09/03/20 10/03/20 11/03/20 12/03/20 13/03/20 14/03/20 15/03/20 16/03/20 17/03/20 18/03/20
Row 2 d d d d d d d d d d
Row 3

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data

See if this does what you want

20 03 30.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Series
2	4	d	d		d	d	d	d		d		d	d	d
Count series

Cell Formulas
Range	Formula
A2	A2	=COUNTIFS(B2:O2,"d",C2:P2,"<>d")

 

[Kipper63]

Apologies, didn't show correctly in original post, please see attachment.

 

[Kipper63]

Thanks Peter, that works great, could you give me a quick explanation as to how it is working, I expected it to be much more difficult?

 

[Peter_SSs]

Thanks Peter, that works great, could you give me a quick explanation as to how it is working, I expected it to be much more difficult?

For the ranges I used it looks at columns B:O and C:P at the relative cells in each range looking for a "d" in the first range and "not d" in the second range.
For example look at the first cell of each range B2="d" and C2 = "d" This does not meet the condition.
Now look at the 2nd cells C2="d" and D2="". This meets the condition so count 1
Keep working across all the other pairs of adjacent columns.

In other words, the function is counting how many times it can find a "last cell in a series" Those last cells are C2, H2, J2 & N2

 

[Kipper63]

Many thanks.

 

[Peter_SSs]

Many thanks.

You're welcome.

 

[Kipper63]

Sorry Peter, can you help me expand it further?
It works exactly as I'd hoped but now I'm wondering if I can use it over a rolling time period.
So I have the data as previously supplied with the date as the column heading.
My dates range from e.g. 01/03/2019 to 31/07/2020.
I have figured out how to do a count of the 'd's using =COUNTIFS(J2:JZ2,"d",J1:JZ1,">="&TODAY()-365) where row 2 contains the recording of 'd' codes and row 1 is the column heading with dates in and this will give me a count of the 'd's looking back over the last 365 days.
But I'm struggling to include the solution you gave me with the rolling date period.
Is it possible to adapt?

 

[Peter_SSs]

Are you simply trying to count "d" vales in the 12 month range or are you still trying to count 'series'?

Also, is it actually 365 days you are looking to count or 1 year (which could be 366 days)?

 

[Kipper63]

I am still trying to count 'series', my formula above counts the number of occurrences of 'd' values.
It is 1 year, I forgot it was a leap year