Excel - Countifs Formula help with Multiple criteria

[Mallesh23]

Hi Team,


Need your help in understanding the countifs formula, when I give More Criteria in Array It gives different result.


Below formula gives Correct result.
=COUNTIFS($H:$H,"South",$I:$I,"NY",$J:$J,"Pending")
=SUM(COUNTIFS($H:$H,{"South","North"},$I:$I,"NY",$J:$J,"Finished"))


Here Below formula not giving correct result. when cross checked with manual filter in excel.


=SUM(COUNTIFS($H:$H,{"South","North"},$I:$I,"NY",$J:$J,{"Finished","Pending"}))
=SUM(COUNTIFS($H:$H,{"South","North","West"},$I:$I,{"NY","London"},$J:$J,"Finished"))


Please suggest correct formula to extract counts using mutliple criteria. Thanks




Thanks in advance for your help.


Regards,
mg

 

[Mallesh23]

Hi Fluff,

I just identified the problem, but not yet sorted, It will be great help if you can correct it. Thanks
Sheet1.Range("m2").Value = Application.Sum(Application.CountIfs(rng1, ar1, rng2, ar2, rng3, "Finished"))

Regards,
mg

 

[Fluff]

Ok, you need to change the 2nd array to

ar2 = Array(Array("NY"), Array("LONDON"))

 

[Mallesh23]

Hi Fluff,
I am too much Glad now , Jumping with Joy. Thanks for your help till end.


Regards,
mg

 

[Fluff]

My pleasure & thanks for the feedback