Countif based on another field

[#1]

Hi,

I have data similarly structured to one below. It has IDN (e.g. time stamp) and sets of persons. Same persons appear in both sets but Set1 is assumed to be the complete list of persons. I have table where

• Set1 is only dimension
• for first column expression is =Count(DISTINCT IDN).
• for second column I would like to have something similar as Excel's countif function =countif(column for Set2 in data,set1 value in result table). For first row (Set1 value is  A) it should thous count how many times A appears in Set2.

Is this possible?

Result table:

Set1	Count set1	Count set2
A	3	4
B	4	2
C	2	3
D	1	0

Data:                                                                                             

IDN	Set1	Set2
1	A
2	A	C
3	A
3	A	B
4	B
5	B	A
6	B
7	B	A
7	B	C
7	B
8	C
9	C	A
9	C	B
10	D	A
10	D	C

[Gysbert_Wassenaar]

See attached qvw

[#3]

Yes, your solution is an answer to my original question. I should have mentioned that the data was only an example structured similarly as my real data that has approx 400 different values corresponding A,B,C,D.... Thus hard coding each of them is not what I want to do.

pick(match(Set1,'A','B','C','D')

,count({<Set2={'A'}>} total IDN)

,count({<Set2={'B'}>} total IDN)

,count({<Set2={'C'}>} total IDN)

,count({<Set2={'D'}>} total IDN)

)

[Gysbert_Wassenaar · Accepted solution]

I've added a second option. See attached qvw.

[#5]

Amazing! Thank you!

I didn't realize that breaking the connection between variables would do the trick.