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!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Levels[/TD]
[TD]Data[/TD]
[TD]To calculate[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]3[/TD]
[TD]COUNTIF(B2:B6,">0.39")*0.5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]COUNTIF(B2:B6,">0.39")*0.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[TD]COUNTIF(B2:B6,">0.39")*0.5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[TD]COUNTIF(B2:B6,">0.39")*0.5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12[/TD]
[TD]COUNTIF(B2:B6,">0.39")*0.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]5[/TD]
[TD]COUNTIF(B9:B10,">0.39")*0.5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]COUNTIF(B9:B10,">0.39")*0.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

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

Any pattern? Always a text like "B1", "B2", "B3"..?

M.

 

[Valkyrie_ride]

Any pattern? Always a text like "B1", "B2", "B3"..?

M.

There's no pattern... these are the levels in a given building.

 

[Marcelo Branco]

See if this does what you need


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Levels​

[/td][td]

Data​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

B1​

[/td][td]

3​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1​

[/td][td]

3​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

2​

[/td][td]

5​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

3​

[/td][td]

8​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

4​

[/td][td]

12​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

xxx​

[/td][td]

5​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

1​

[/td][td]

5​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

yyy​

[/td][td]

4​

[/td][td]

0,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

1​

[/td][td]

0,2​

[/td][td]

0,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in C2 copied down
=IF(A2="","",IF(N(B1)=0,COUNTIF(B2:INDEX(B2:B$1000,MATCH(TRUE,B2:B$1000="",0)),">0,39")*0.5,C1))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Valkyrie_ride]

See if this does what you need


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Levels​

[/td][td]

Data​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

B1​

[/td][td]

3​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1​

[/td][td]

3​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

2​

[/td][td]

5​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

3​

[/td][td]

8​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

4​

[/td][td]

12​

[/td][td]

2,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

xxx​

[/td][td]

5​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

1​

[/td][td]

5​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

yyy​

[/td][td]

4​

[/td][td]

0,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

1​

[/td][td]

0,2​

[/td][td]

0,5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in C2 copied down
=IF(A2="","",IF(N(B1)=0,COUNTIF(B2:INDEX(B2:B$1000,MATCH(TRUE,B2:B$1000="",0)),">0,39")*0.5,C1))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

Yes it helps! Thank you!