Count values in Col A with criteria in Col B

[drom]

Hi and Thanks in advance!

I have 3 named and Dinamic formulas:

• rcColA 'Shows Countries
• rcColB 'Cities
• rcColC 'Diseases

In Cell A1 I would like to count how many different diseases I have eg in Spain
Since in many cities we have the same disease, I do not want to count the same disease more than one time
ps:
I do not have Office 365
I do not wat to use a Pivot table
I do not want to use extra columns

 

[Saba Sabaratnam]

Hi

Hopefully, you can adapt this formula to solve your problem.

I have the following data and spreadsheet layout.

I entered the following array formula in E2 by pressing Shift + Control + Enter together.

=SUM(--(FREQUENCY(IF(A1:A22=D2,MATCH(C1:C22,C1:C22,0)),ROW(B1:B22)-1)>0))

Kind regards

Saba