I have looked into this for days.
I am trying to get a count in a 2d array using 2 separate criteria.
eg:
array is a dynamic array t1(x , y)
contains:
t1(x,0) = serial date,
t1(x,1) = function - eg: "Chg To"
.
.
.
t1(x,11) = code - eg: "C01"
Note awf = application.worksheet function
Eg:
' counts correctly (3 "C01" 's) if only 1 criteria
r = Application.Count(Application.Match(awf.Index(t1(), 0, 11), Array("C01"), 0))
'if I use 2 criteria (code field and function field) does not even count
r = Application.Countifs(Application.Match(awf.Index(t1(), 0, 11), 0), Array("C01"), Application.Match(awf.Index(t1(), 0, 2), 0), Array("Chg To"))what it should be is 2 (entries with "C01" and "Chg To")
see sample data.
I prefer not to use a loop!
The reason I am now using arrays
I had office 2010 and used to extract this data from a spread sheet and everything worked well.
When I upgraded to office 2019, everything slowed right down.
So I converted my data collection spread sheets that are large to arrays which greatly speeded up the data collection.
I converted all but the one that requires 2 criteria to be counted (this one in this post)
Any help would be appreciated.
Sample data:
42489.6686100694,"Chg From ","E - entry",2016,"Vnse","*",,,,"rabsofty","C06"
42489.6686168981,"Chg To","E - entry",2016,"Weto","W",,,,"rabsofty","C08"
42489.6694688657,"Chg From ","E - entry",2016,"Lnse","Bi",,,,"rabsofty","C06"
42489.6694755787,"Chg To","E - entry",2016,"Lotense","Bi",,,,"rabsofty","C08"
42489.6716525463,"Chg From ","E - entry",2016,"Wto","W",,,,"rabsofty","C06"
42489.6716594907,"Chg To","E - entry",2016,"Wep","W",,,,"rabsofty","C08"
42489.6722805556,"Chg From ","I - entry",2016,"Ve","*",,,,"rabsofty","C05"
42489.6722875,"Chg To","I - entry",2016,"WInc","W",,,,"rabsofty","C07"
42601.6604346065,"Del","E - entry",2016,"Vfr","Y",,,,"rabsofty","C04"
42400.2868357639,"Chg To","E - entry",2016,"exp","W",,,,"rabsofty","C01"
42400.2868357639,"Chg To","E - entry",2016,"Maxp","W",,,,"rabsofty","C01"
42400.2868357639,"Cxx To","E - entry",2016,"Weaexp","W",,,,"rabsofty","C01"