Excel - Countifs Formula help with Multiple criteria

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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"))
 
Upvote 0
Hi Fluff,

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

Regards,
mg
 
Last edited:
Upvote 0
You need to use it when you have an array of criteria (ie the bit in the {})
 
Upvote 0
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
 
Upvote 0
Are you trying to put the formula in the cell, or just the result?
 
Upvote 0
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
 
Upvote 0
How about
Code:
Sheet1.Range("m2").Value = Application.Sum(Application.CountIfs(rng1, ar1, rng2, ar2, rng3, "Finished"))
 
Upvote 0
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
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top