Countif until blank cell reached repeatedly in sheet

[Valkyrie_ride]

Dear all

Would like to seek help to Countif a range of cells within tables (that are not formatted as tables due to design needs) in an Excel sheet.
The problem is that the tables do not have the same amount of rows, so if we drag the formula down, it will not work. I thought of using OFFSET or INDEX MATCH, but I am not sure how to make use those to achieve what I want.

This is what I hoped to achieve in the 'To calculate' column. You can see that there are two separate tables and I want the countifs to start from the non-blank cells, stop at a blank cell, then re-start from the next non-blank cell.

I would appreciate it if anyone can guide me on this!

Levels	Data	To calculate
B1	3	COUNTIF(B2:B6,">0.39")*0.5
1	3	COUNTIF(B2:B6,">0.39")*0.5
2	5	COUNTIF(B2:B6,">0.39")*0.5
3	8	COUNTIF(B2:B6,">0.39")*0.5
4	12	COUNTIF(B2:B6,">0.39")*0.5
B1	5	COUNTIF(B9:B10,">0.39")*0.5
1	5	COUNTIF(B9:B10,">0.39")*0.5

<tbody>
</tbody>

 

[wideboydixon]

I can't help feeling I've over-complicated this!

Book1
	A	B	C
1	Levels	Data	To calculate
2	B1	3	2.5
3	1	3	2.5
4	2	5	2.5
5	3	8	2.5
6	4	12	2.5
7
8
9	B1	5	1
10	1	5	1
Sheet1

Cell Formulas
Range	Formula
C2	{=IF($A2="","",COUNTIF(OFFSET($B2,MAX(IF(ROW($A$1:$A$3000)),IF($A$1:$A$3000="Levels",ROW($A$1:$A$3000),IF($A$1:$A$3000="",ROW($A$1:$A$3000)))))-ROW()+1,0,MIN(IF(ROW($A$1:$A$3000)>ROW(),IF($A$1:$A$3000="",ROW($A$1:$A$3000))))-ROW()-(MAX(IF(ROW($A$1:$A$3000)),IF($A$1:$A$3000="Levels",ROW($A$1:$A$3000),IF($A$1:$A$3000="",ROW($A$1:$A$3000)))))-ROW()+1),1),">0.39")*0.5)}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Marcelo Branco]

Question:
Is the first value of each table in the Levels column always "B1"?

M.

 

[Valkyrie_ride]

Hi,

For me, it is not always B1. For example, in the example above, it's B2 and B9 respectively.

 

[Fazza]

I don't know what version you're using. Or if a helper column is OK.

Helper, C2 and down =C1+NOT(AND(LEN(B1),LEN(B2)))
then in D2 and down =COUNTIFS(C:C,C2,B:B,">0.39")*0.5

OK?

 

[Valkyrie_ride]

Hi @Fazza, I am looking into your solution: thank you!

However, I would like to understand what is the use of the helper column: what does it do?

thanks for explaining to a beginner!

 

[Fazza]

the helper creates an identifier unique for each range

 

[Valkyrie_ride]

Thank you for the explanation!

I tried this but it didn't work... what I am looking for is a result like this:

Levels	Data	Calculation
B1	3	2.5
1	3	2.5
2	5	2.5
3	8	2.5
4	12	2.5
B1	5	1
1	5	1

Using your way, I got 3.5 all the way; not sure if I did anything wrong...

 

[Marcelo Branco]

Hi,

For me, it is not always B1. For example, in the example above, it's B2 and B9 respectively.

I'm asking about the first value in column A (Levels) for each table (group of cells), not the first cell in column B.
Is it always a text ("B1")?

M.

 

[Valkyrie_ride]

I'm asking about the first value in column A (Levels) for each table (group of cells), not the first cell in column B.
Is it always a text ("B1")?

M.

Hi, sorry to mis-read... Nope it is not always B1