Counting series' of text within a row

Kipper63

New Member
Joined
Mar 30, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1Series
24dddddddddd
Count series
Cell Formulas
RangeFormula
A2A2=COUNTIFS(B2:O2,"d",C2:P2,"<>d")
 

Kipper63

New Member
Joined
Mar 30, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Apologies, didn't show correctly in original post, please see attachment.

Data series.png
 

Kipper63

New Member
Joined
Mar 30, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 
Last edited:

Kipper63

New Member
Joined
Mar 30, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 30, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,421
Messages
5,547,823
Members
410,813
Latest member
Vhinzvirgo
Top