Count occurrence of specific value in a list, upwardly of given number

[ibmy]

Hi,

Specific Value = 0.1

5.17.xlsb
	B	C	D
1	Data	Number	Result
2	0.4
3	0.1
4	0.2
5	0.1	3	2
6	0.5
7	0.1	1	1
8	0.5
9	0.1
10	0.2
11	0.2
12	0.1
13	0.1
14	0.3
15	0.1
16	0.2	8	4
17	0.9
to ask

Description:
1. C5 number is 3, so count 0.1 in B5,B4,B3
2. C7 number is 1, so just count 0.1 in B7 only
3. C16 number is 8, so count 0.1 from B16 -> B9

No count if there is a blank cell in Column C.

Table above is sample of 65k row data.

 

[jaeiow]

=IF(ISBLANK(B2),"",COUNTIF(OFFSET(A2,-B2,):A2,0.1))

 

[Fluff]

Another option

Fluff.xlsm
	B	C	D
1	Data	Number	Result
2	0.4
3	0.1
4	0.2
5	0.1	3	2
6	0.5
7	0.1	1	1
8	0.5
9	0.1
10	0.2
11	0.2
12	0.1
13	0.1
14	0.3
15	0.1
16	0.2	8	4
17	0.9
Main

Cell Formulas
Range	Formula
D2:D16	D2	=IF(C2="","",COUNTIFS(INDEX(B:B,ROWS(D$2:D2)-C2+1):B2,0.1))

 

[jaeiow]

@Fluff This was off a column, maybe an xlbb thing

1.

=IF(C2="","",COUNTIFS(INDEX(B:B,ROWS(D$2:D2)-C2+1):B2,0.1))

2.

=IF(B2="","",COUNTIFS(INDEX(A:A,ROWS(C$2:C2)-B2+1):A2,0.1))

 

[Fluff]

No it's not off if you look at both the mini-sheet provided by the OP & mine, you will see that it starts in B1. That is one of good things about the XL2BB add-in, as you can see which cells are which.

 

[jaeiow]

Ah okay, I was going off of what I put down yesterday (which was off). the addin doesn't work on my work computer, never used it and just started here. Didn't see that.