Counting occurence of multiple cells containing data

[BrakZak]

I have a worksheet that has rows of data, that are made up of blank cells or cells containing a single letter (see screenshot example). The cells I'm interested in are groups of 6 or 7 cells that contain the letters "d", "a" or "n".

I need to count how many of these groups of 6 and how many groups of 7 cells exist in a row. Note that often such a group is followed by cell containing a letter "U", these should be considered as a blank cell (and not counted).

There are also groups of cells containing the letter "v", these should not be counted as well.

Hope this makes sense

[corinereyes]

Hi BrakZak,

Try the COUNTIF function.

[BrakZak]

How can I use COUNTIF to count the number of groups of cells? I don't want to count the total number of cells containing "d", "a" or "n", but the number of groups of 6 or 7 cells that are in the sheet.

[corinereyes]

Hi BrakZak,

I'm not sure of this but on cell V1 try:

=(COUNTA(A1:T1)-SUM(COUNTIF(A1:T1,"v"),COUNTIF(A1:T1,"U")))/6

change it to /7, if you want a group of 7.

assuming that your data is in A1:t15 ->
the above formula works per row, to include the whole range, change it to A1:T15

=(COUNTA(A1:T15)-SUM(COUNTIF(A1:T15,"v"),COUNTIF(A1:T15,"U")))/6

[BrakZak]

=(COUNTA(A1:T1)-SUM(COUNTIF(A1:T1,"v"),COUNTIF(A1:T1,"U")))/6

Counts the total number of cells with the mentioned letters, but then just divides it by 6. This will not return the value I need.

Example: in a row there are three groups of 6 cells containing "d", "a" or "n", and one group of 7. Your formula would return 4.166667 ((6+6+6+7)/6). However, I need it to return either 3 or 1 (depending wether I'm looking for groups of 6 or 7).

[corinereyes]

=(COUNTA(A1:T1)-SUM(COUNTIF(A1:T1,"v"),COUNTIF(A1:T1,"U")))/6

fyi, the above formula, counts the number of non-blank cells minus (-) cells that contains "v" & cells that contains "U".

In your example, if there are 20 cells (i.e. A1:T1), 6 cells are blank, 10 cells contain "d", 2 cells contain "v" and 2 cells contain "U",

so:

= (14 cells that contains text - (2 "v" + 2 "U"))/ 6 = 1.7

can you post an example file and the expected results?

[Ron Coderre]

EDITED: I'd forgotten to mention what $X$1:$Z$1 referred to!

OK.....I think I have something you can work with.

Using the information you posted
AND
assuming that range is in A1:T17
AND
X1: a
Y1: d
Z1: n

Then

This ARRAY FORMULA counts the groups of cells in the referenced row 
where SIX consecutive cells equal "a" or "d" or "n"

in cell V1:
=SUMPRODUCT(--(FREQUENCY(IF(--ISNUMBER(MATCH(A1:T1,$X$1:$Z$1,0)),COLUMN(A1:T1)),IF(--ISNUMBER(MATCH(A1:T1,$X$1:$Z$1,0))=0,COLUMN(A1:T1)))=6))

ALTERNATIVELY, if you don't want to look-up a, d, and n in other cells, you can embed them in the formula:
V1: =SUMPRODUCT(--(FREQUENCY(IF(--ISNUMBER(MATCH(A1:T1,{"A","D","N"},0)),COLUMN(A1:T1)),IF(--ISNUMBER(MATCH(A1:T1,{"A","D","N"},0))=0,COLUMN(A1:T1)))=6))

Copy V1 into cell V3, V5, etc

Note_1:
ARRAY FORMULAS are committed with CTRL+SHIFT+ENTER, 
instead of just ENTER

NOTE_2: 
To count the groups of SEVEN, change the final 6 to a 7.

In my testing, the formula returned these counts of 6's:
V1	0
V3	1
V5	2
V7	2
V9	2
V11	1
V13	0
V15	2
V17	1

When adjusted for 7's...only the last formula returned a 1, the others returned 0's

Does that do what you want?
Post back if you have more questions.

[BrakZak]

EDITED: I'd forgotten to mention what $X$1:$Z$1 referred to!

OK.....I think I have something you can work with.

Using the information you posted
AND
assuming that range is in A1:T17
AND
X1: a
Y1: d
Z1: n

Then

This ARRAY FORMULA counts the groups of cells in the referenced row 
where SIX consecutive cells equal "a" or "d" or "n"

in cell V1:
=SUMPRODUCT(--(FREQUENCY(IF(--ISNUMBER(MATCH(A1:T1,$X$1:$Z$1,0)),COLUMN(A1:T1)),IF(--ISNUMBER(MATCH(A1:T1,$X$1:$Z$1,0))=0,COLUMN(A1:T1)))=6))

ALTERNATIVELY, if you don't want to look-up a, d, and n in other cells, you can embed them in the formula:
V1: =SUMPRODUCT(--(FREQUENCY(IF(--ISNUMBER(MATCH(A1:T1,{"A","D","N"},0)),COLUMN(A1:T1)),IF(--ISNUMBER(MATCH(A1:T1,{"A","D","N"},0))=0,COLUMN(A1:T1)))=6))

Copy V1 into cell V3, V5, etc

Note_1:
ARRAY FORMULAS are committed with CTRL+SHIFT+ENTER, 
instead of just ENTER

NOTE_2: 
To count the groups of SEVEN, change the final 6 to a 7.

In my testing, the formula returned these counts of 6's:
V1	0
V3	1
V5	2
V7	2
V9	2
V11	1
V13	0
V15	2
V17	1

When adjusted for 7's...only the last formula returned a 1, the others returned 0's

Does that do what you want?
Post back if you have more questions.

You, my friend, are a genius. Exactly what I needed.

Both your help is very much appreciated.

[Ron Coderre]

I'm glad I could help.....(this one was fun!)

...and thanks so much for the kind words.