multiple criteria to select number

Copper Contributor

I have one data set COUNTIFS(A1:D10, Berwick)

If Berwick appears it would be classed as 1

In a the same formula set, I would like to count : E1:F10

when "Berwick" and "Clinic visit" appear 

if these two appear it will be classed as 1

if they appear in the data set twice it would be counted as 2


So in the one formula:

A1:D110, Berwick

E1:f10 Berwick, Clinic visit



column data A1:d10

Berwick is listed 3 times

column data E1:f10

Berwick (E:2), Clinic visit  (F:5)  (different cells) is listed 2 times


The formula = 5

6 Replies


Above words could be located in any cells like


or data is structured somehow?


@Sergei Baklan 


Data structured see attached thanks for helping







So, "Clinic visit", if appears, always in C1 and it is applied to all names in that column, not only to Berwick?

@Sergei Baklan yes correct


As variant

=IF( $C$1="Clinic visit", COUNTIF($A$2:$B$9, "berwick"),  COUNTIF($A$2:$D$9, "berwick") )

 thankyou @Sergei Baklan