countifs more than 2 criteria

[Vitor Leone]

Hello,

I am trying to count the type of management type using the below table using the countifs function but everytime I use more than 2 criteria I got a 0 value.

I used the following =countifs(range,"=Trust", range,"=Adur",range="2009").

Meaning I want all Trust, when the borough is Adur in 2009.

Where am I going wrong? Does the countifs function cannot handle more than 2 criteria.

Any help will be very much appreciatted.

Year	Borough	Management Type
2009	Adur	Trust
2009	Adur	Trust
2009	Adur	Trust
2009	Allerdale	Education
2009	Allerdale	Trust
2009	Allerdale	Education
2009	Allerdale	Education
2009	Allerdale	In-House
2009	Allerdale	Trust
2009	Allerdale	Trust
2009	Allerdale	In-House
2009	Allerdale	Education
2010	Adur	Trust
2010	Adur	Trust
2010	Adur	Trust
2010	Adur	Education
2010	Allerdale	Education
2010	Allerdale	Trust
2010	Allerdale	Education
2010	Allerdale	Education
2010	Allerdale	In-House
2010	Allerdale	Trust
2010	Allerdale	Trust
2010	Allerdale	Education
2010	Allerdale	Education
2010	Alnwick	Trust
2011	Adur	Trust
2011	Adur	Trust
2011	Adur	Trust
2011	Adur	Education
2011	Allerdale	Education
2011	Allerdale	Trust
2011	Allerdale	Education
2011	Allerdale	Education
2011	Allerdale	In-House
2011	Allerdale	Trust
2011	Allerdale	Trust
2011	Allerdale	Education
2011	Allerdale	Education

[#2]

Hi Vitor,

As i remember COUNTIFS allows up to 256 criterias. In your latest condition use "=2009"

[#3]

Dear Sergei,

Many thanks for your reply.

Still does not work. Each criterion is coming from a different column so column A will be the year range, column B borough, and column C management type. When I type the function countifs

the syntax I am using is:

=countifs(column c range,"=Trust", column b range,="Adur", column a range, "=2009")

And I get a zero value...

[#4]

Hi Vitor,

Please see file  attached. I used

=COUNTIFS(C2:C40,"Trust*",B2:B40,"Adur*",A2:A40,"=2009")

Trust*, Adur* since there are spaces at the end of strings, not sure that's in your file or they were added when i copy/pasted from screen. 

[#5]

Hi @Vitor César,

There is no problem. Select Correct range (either specified cells or entire column) and apply

=COUNTIFS(C:C,"Trust",B:B,"Adur",A:A,"2009")

I've taken your data as A,B,C Columns respectively.

[#6]

Yes @Vitor Leone, Check you second column and third column.

As per @Sergei Baklan view, I think they may contains spaces.