Rangecount

[yvonne-c]

I am having an issue getting rangecount to work

I am using the following expression to count incidences across multiple columns which gives me the results below

Sum(RangeCount(L1,L2,L3,L4,L5,L6,IA,IB,IC,M1,M2,M3,M4,M5,M6))

Les	Count
OT	5
-	3
WLD	2
BR	1
Total	11

It works fine until there are more than one 'Les' on a row -  then they are totalled on the '-' row.

I want to count each 'les' individually so the results should be as follows

Les	Count
OT	5
WLD	3
BR	2
IB	1
Total	11

An example of my data is attached

Any ideas?

Thanks

[hic]

How is "Les" defined? I suspect that the definition is made so that NULL is returned when there are several values.

HIC

[yvonne-c]

Les is a dimension

[hic]

Yes of course it is. But how is it defined? What formula have you used?

[yvonne-c]

Sorry I'm not sure what you mean!

My list of 'Les' are stored on a separate table?

[hic · Accepted solution]

I would use the following Load statement to load the data. This will define a field called "Les".

Crosstable (Column,Les)
LOAD
RecNo() as ID,
L1,L2,L3,L4,L5,L6,IA,IB,IC,M1,M2,M3,M4,M5,M6
FROM [example.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Then I would create a chart with "Les" as dimension and Count(ID) as expression.

HIC

[yvonne-c]

Thanks, that's worked for me