Multi COUNTIFS criteria & ranges

[zippyste]

Hi,

I am trying to create an COUNTIFS fourmula to count multiple columns which contain multiple values.

If I filter my spreahseet so column T =1, column N = "Quoted","Won" or "Lost" and column E= "Customer","Tier One" or "New enquirer". I get a row count of 94.

If I use the following formula in a cell

=SUM(COUNTIFS(T7:T1462,"1",N7:N1462,{"Quoted","Won","Lost"},E7:E1462,{"Customer","Tier One","New enquirer"}))

I get 35 !

Is it not possible to have multiple criteria on multiple ranges as in above formula ?

I also get 94 if I use a pivot table

Any help much appreciated

[BadlySpelledBuoy]

Not tested as no sample workbook provided, but try changing the commas to semicolons as marked in red below:

Please Login or Register  to view this content.

BSB

[zippyste]

Thank you BSB. That done the trick.

[BadlySpelledBuoy]

Glad I could help.

If you want a very good explanation of why it works, try this:
https://excelxor.com/2014/09/28/coun...iteria_ranges/

BSB