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

 

[Fluff]

Try
=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"))

 

[Mallesh23]

Hi Fluff,

Thank you so much for your help, It worked,
One more question - When to use sum along with Countifs?....

Regards,
mg

 

[Fluff]

You need to use it when you have an array of criteria (ie the bit in the {})

 

[Mallesh23]

Hi Fluff,
Thank you so much for your help one more help if you can ,
I am trying same formula using array, But its throwing Error.Below is my attempted code


Sheet1.Range("m2").Value = "=SUM(COUNTIFS(" & rng1 & "," & ar1 & "," & rng2 & "," & ar2 & "," & rng3 & ",""Finished""))"




Sub Countifs_with_Array()
Dim lr As Long
Dim ar1, ar2, ar3 As Variant
Dim rng1, rng2, rng3 As Range


ar1 = Array("South", "North")
ar2 = Array("NY", "LONDON")
lr = Sheet1.Range("h1000").End(xlUp).Row


Set rng1 = Sheet1.Range("h1:h" & lr)
Set rng2 = Sheet1.Range("I1:I" & lr)
Set rng3 = Sheet1.Range("J1:J" & lr)


This is my Excel Formula,
'=SUM(COUNTIFS($H:$H,{"South","North"},$I:$I,{"NY";"London"},$J:$J,"Finished")) Excel Formula


Recorded Formula
'Sheet1.Range("m2").Value = "=SUM(COUNTIFS(C8,{""South"",""North""},C9,{""NY"";""London""},C10,""Finished""))" 'Recorded Formula


Below is my Attempted formula, by passing range and criteria via Array
Sheet1.Range("m2").Value = "=SUM(COUNTIFS(" & rng1 & "," & ar1 & "," & rng2 & "," & ar2 & "," & rng3 & ",""Finished""))"


My attempted formula is throwing Error. How to Fix it. thanks.


End Sub

Thanks in advance

Regards,
Mallesh

 

[Fluff]

Are you trying to put the formula in the cell, or just the result?

 

[Mallesh23]

Hi Fluff,

I am looking for countifs result which comes in numbers in any cell or in message box,

I am taking 3 columns in array and criteria to search is also in array, and expecting result

I have huge data, I will modify as per my requirement. Thanks



Regards,
mg

 

[Fluff]

How about

Sheet1.Range("m2").Value = Application.Sum(Application.CountIfs(rng1, ar1, rng2, ar2, rng3, "Finished"))

 

[Mallesh23]

Hi Fluff,


Thanks once again for your help, Tested suggested formula with manually filtering.
It is putting SemiColon on all Array Criteria , Actually It should be on Second Criteria onword I Think.



The formula suggested its result matches When I put all semicolon in Criteria. its not showing correct result.


=SUM(COUNTIFS(H:H,{"North";"South"},I:I,{"London";"NY"},$J:$J,"Finished"))
Sheet1.Range("m2").Value = Application.Sum(Application.CountIfs(rng1, ar1, rng2, ar2, rng3, "Finished"))



Here with Single Semicolon in Second array Criteria it is providing result.


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


Thanks
mg

 

[Fluff]

Glad you sorted it & thanks for the feedback