Excel: Frequency of variables using countif with &

0
votes

I have two excel sheets. I am finding the frequency of variable school IDs from sheet 1 into sheet2. Lets say a student volunteers for some activity at two schools. So there is an &. I want countif to count schools even where there is &

sheet1

   A               B
1 Student name   School ID
2 S1              Sch333 & Sch209
3 S2              Sch209
4 S3              Sch333
5 S4              Sch209 & Sch111
6 S5              Sch209

sheet2

 A             B
1 School ID     Frequency
2 Sch209
3 Sch333
4 Sch111

Formula for frequency of school IDs in cell B2 of sheet2

 =COUNTIFS('sheet1'!$b:$b,'sheet2'!a2)

This ignores the rows where there is &. Expected output

         A             B
    1 School ID     Frequency
    2 Sch209          4
    3 Sch333          2
    4 Sch111          1
3
excelvbaexcel-formulacountif

3 Answers

2
votes

You can sum the counts for the value alone plus before or after " & "

enter image description here

1
votes

Maybe,

In sheet2, B2 array (CSE) formula copied down :

=COUNT(SEARCH(A2,'sheet1'!$B$2:$B$100))

Remark : Array (CSE) formula to be confirmed by "Ctrl"+"Shift"+"Enter" to enter it.

1
votes

According to @shrivallabha.redij, you can remplace your formula

=COUNTIFS('sheet1'!$b:$b,'sheet2'!a2)

by this one:

=COUNTIFS('sheet1'!$b:$b,"*" & 'Sheet2'!a2 & "*")