# multiple criteria to select number

Copper Contributor

# multiple criteria to select number

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

Example

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

# Re: multiple criteria to select number

Above words could be located in any cells like

or data is structured somehow?

# Re: multiple criteria to select number

Data structured see attached thanks for helping

# Re: multiple criteria to select number

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

# Re: multiple criteria to select number

@SergeiBaklan yes correct

# Re: multiple criteria to select number

As variant

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

# Re: multiple criteria to select number

thankyou @SergeiBaklan