COUNTIF Issue - Multiple Criteria

[MCKSD1524]

HI

I'm having issues with a COUNTIF formula where i have it check certain columns for mulltiple values. Although its a valid formula the result is incorrect.

The formula is =SUM(COUNTIFS(Table316[Tier],{"Tier 1","Tier 2","Tier 3","Tier 4"},Table316[Zone],{"DOMAIN A","DOMAIN B"},Table316[Sec/Crit Needed],">10"))

The resultant reply should be 44 but this comes back as 1.

If I split the formula it brings back the correct values which then sum upto the expected value of 44

=SUM(COUNTIFS(Table316[Tier],{"Tier 1","Tier 2","Tier 3","Tier 4"},Table316[Zone],"DOMAINA",Table316[Sec/Crit Needed],">10")) Result = 0
=SUM(COUNTIFS(Table316[Tier],{"Tier 1","Tier 2","Tier 3","Tier 4"},Table316[Zone],"DOMAINB",Table316[Sec/Crit Needed],">10")) Result = 44

I've worked around it so far by adding the 2 formulas together as below but I can't see why the top formula wouldn't work. When I use the same formula to count colums with a certian tier "Table316[Tier],{"Tier 1","Tier 2","Tier 3","Tier 4"}" it works fine.

Current Fix

=SUM(COUNTIFS(Table316[Tier],{"Tier 1","Tier 2","Tier 3","Tier 4"},Table316[Zone],{"DOMAIN A"},Table316[Sec/Crit Needed],">10"))+SUM(COUNTIFS(Table316[Tier],{"Tier 1","Tier 2","Tier 3","Tier 4"},Table316[Zone],{"DOMAIN B"},Table316[Sec/Crit Needed],">10"))

 

[Marcelo Branco]

Welcome to Mr Excel

The first criteria is an horizontal array (values separated by commas); so the second array must be a vertical array (values separated by semicolons)

Try this
=SUM(COUNTIFS(Table316[Tier],{"Tier 1","Tier 2","Tier 3","Tier 4"},Table316[Zone],{"DOMAIN A";"DOMAIN B"},Table316[Sec/Crit Needed],">10"))

Hope this helps

M.

 

[MCKSD1524]

FANTASTIC, worked perfectly.

Without sounding too much like a newbie what's the difference between a horizontal and a vertical array?

Thanks Marcelo!!

 

[Marcelo Branco]

You are welcome.

About Arrays Constants: take a look at
https://support.office.com/en-us/ar...formulas-477443ea-5e71-4242-877d-fcae47454eb8

M.