Max wrote...
Perhaps a quick and easy way is simply to use a Pivot Table (PT) ?
....
You don't believe in reading OPs' postings carefully? How would pivot
tables be able to identify the entries in cells above cells evaluating
to "x"?
Using a different list, such as
1
2
x
1
2
3
4
x
1
x
1
2
x
For starters, the OP is requesting to identify 2, 4 and 1 as
categories. Then the OP is requesting to count either the 2s, 4s and 1s
immediately above the cells containing the x's or could all 2s, 4s and
1s in the list.
First, identifying the cells immediately above x's. If the list were
named LST and the category values would appear in column C starting in
cell C1, enter the formulas
C1 [array formula]:
=INDEX(LST,MATCH(TRUE,(OFFSET(LST,1,0)="x"),0))
C2 [array formula]:
=INDEX(LST,MATCH(1,(OFFSET(LST,1,0)="x")*(COUNTIF(C$1:C1,LST)=0),0))
Fill C2 down until the formula returns #N/A. Using my sample data,
there would be only 3 categories - 2, 4 and 1 - so C1:C3 would evaluate
to 2, 4 and 1, respectively, and C4 to #N/A.
To count just the 2s, 4s and 1s appearing immediately above the x's,
select D1
3 and use FREQUENCY.
D1
3 [array formula]:
=FREQUENCY(IF(OFFSET(LST,1,0)="x",LST),C1:C3)
To count all 2s, 4s and 1s in LST, use COUNTIF.
D1:
=COUNTIF(LST,C1)
and fill D1 down into D2
3.
