Countif in arrays with two conditions
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Countif in arrays with two conditions
Hello everyone
Mr. Rudi has helped me in similar issue at this link
http://www.eileenslounge.com/viewtopic.php?f=30&t=25266" onclick="window.open(this.href);return false;
But I don't need to rely on cells (colored cells are not required)
First have a look at this code that count the range("B8:AP8") on a criteria 4,3,2,1,0 ..Run the demo code to see the results in range("AR7:AV8")
I need to extend the criteria ..
To count if :
---------------
range("B8:AP8") on a criteria 4,3,2,1,0
range("B7:AP7") has one of the values in range("B1:F1")
The expected results would be like that snapshot for example : the number 3 happened 13 times in range("B8:AP8")
And in range("X7:L7") the numbers 16 & 18 & 26 & 28 occurs in this range
So the result for 3 is 4
Hope it is clear
Mr. Rudi has helped me in similar issue at this link
http://www.eileenslounge.com/viewtopic.php?f=30&t=25266" onclick="window.open(this.href);return false;
But I don't need to rely on cells (colored cells are not required)
First have a look at this code that count the range("B8:AP8") on a criteria 4,3,2,1,0 ..Run the demo code to see the results in range("AR7:AV8")
I need to extend the criteria ..
To count if :
---------------
range("B8:AP8") on a criteria 4,3,2,1,0
range("B7:AP7") has one of the values in range("B1:F1")
The expected results would be like that snapshot for example : the number 3 happened 13 times in range("B8:AP8")
And in range("X7:L7") the numbers 16 & 18 & 26 & 28 occurs in this range
So the result for 3 is 4
Hope it is clear
You do not have the required permissions to view the files attached to this post.
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
I have posted the same issue here
http://www.excelforum.com/showthread.ph ... ost4530983" onclick="window.open(this.href);return false;
http://www.excelforum.com/showthread.ph ... ost4530983" onclick="window.open(this.href);return false;
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Countif in arrays with two conditions
Here is a modified version of Rudi's macro:
Code: Select all
Sub Demo()
Dim arr
Dim arr2
Dim i As Long
Dim j As Long
Dim temp(0 To 1, 0 To 4)
Dim CountInArray As Long
Application.ScreenUpdating = False
arr = Range("B7:AP8").Value
arr2 = Range("B1:F1").Value
For i = 0 To 4
CountInArray = 0
temp(0, 4 - i) = i
For j = LBound(arr, 2) To UBound(arr, 2)
If Not IsError(Application.Match(arr(1, j), arr2, 0)) And arr(2, j) = i Then
CountInArray = CountInArray + 1
End If
Next j
temp(1, 4 - i) = CountInArray
Next i
Range("AR7").Resize(UBound(temp, 1) + 1, UBound(temp, 2) + 1).Value = temp
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
That's wonderful Mr. Hans
Thank you very very much for that great solution
I will try to work out the original file and see if I could twist it to suit my issue or not
Best Regards
Thank you very very much for that great solution
I will try to work out the original file and see if I could twist it to suit my issue or not
Best Regards
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
Hello again Mr. Hans
Is it possible to edit the UDF implemented in the first post itself ..?
You can change the parameters of the udf to suit
Is it possible to edit the UDF implemented in the first post itself ..?
You can change the parameters of the udf to suit
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Countif in arrays with two conditions
Here you go:
Enter 0 to 4 in AR7:AV7.
Enter the following formula in AR8:
=CountMatch($B$7:$AP$7,$B$1:$F$1,$B$8:$AP$8,AR7)
Fill to the right to AV8.
Code: Select all
Function CountMatch(range1 As Range, matchrange As Range, range2 As Range, criteria As Variant)
Dim i As Long
Dim lngCount As Long
If range1.Cells.Count <> range2.Cells.Count Then
CountMatch = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To range1.Count
If range2(i).Value = criteria Then
If Not IsError(Application.Match(range1(i).Value, matchrange, 0)) Then
lngCount = lngCount + 1
End If
End If
Next i
CountMatch = lngCount
End Function
Enter the following formula in AR8:
=CountMatch($B$7:$AP$7,$B$1:$F$1,$B$8:$AP$8,AR7)
Fill to the right to AV8.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
That's really wonderful solution
You are great and brilliant
Can I use array parameters in the udf?
In fact I need the udf to deal with arrays not ranges in direct way so if you looked at the code in first post, you will notice the udf deals with the function
and I need the same with the new udf .. I mean to play it with arrays
I imagine :
CountMatch(arr,arrCrit,i)
arr >> range("B7:AP8")
arrCrit >> range("B1:F1")
i >> for numbers from 4 to 0
Thanks a lot for great help
You are great and brilliant
Can I use array parameters in the udf?
In fact I need the udf to deal with arrays not ranges in direct way so if you looked at the code in first post, you will notice the udf deals with the function
and I need the same with the new udf .. I mean to play it with arrays
I imagine :
CountMatch(arr,arrCrit,i)
arr >> range("B7:AP8")
arrCrit >> range("B1:F1")
i >> for numbers from 4 to 0
Thanks a lot for great help
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Countif in arrays with two conditions
You should be able to work that out yourself; I have given you the basic idea.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
Since your last post and I was trying but no way. It doesn't work for me
I am not expert like you. I am just amateur
I am not expert like you. I am just amateur
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
All what I can do is that to replace ranges with arrays
But I don't need to deal each row separately
I need to use one array for data and one for criteria
Code: Select all
Function CountMatch(arr As Range, arrCrit As Range, arr2 As Range, criteria As Variant)
Dim i As Long
Dim lngCount As Long
If arr.Cells.Count <> arr2.Cells.Count Then
CountMatch = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To arr.Count
If arr2(i).Value = criteria Then
If Not IsError(Application.Match(arr(i).Value, arrCrit, 0)) Then
lngCount = lngCount + 1
End If
End If
Next i
CountMatch = lngCount
End Function
I need to use one array for data and one for criteria
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
Can you fix that for me (It gives me error)
Code: Select all
Function CountMatch(arr As Range, arrCrit As Range, criteria As Variant)
Dim i As Long
Dim lngCount As Long
For i = LBound(arr, 2) To UBound(arr, 2)
If arr(2, i).Value = criteria Then
If Not IsError(Application.Match(arr(1, i).Value, arrCrit, 0)) Then
lngCount = lngCount + 1
End If
End If
Next i
CountMatch = lngCount
End Function
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Countif in arrays with two conditions
My attempt...
In cell AR8, type: =CountMatchArr($B$8:$AP$8,$B$7:$AP$7,AR7)
In cell AR8, type: =CountMatchArr($B$8:$AP$8,$B$7:$AP$7,AR7)
Code: Select all
Function CountMatchArr(cat As Range, val As Range, crit As Range) As Variant
Dim c, v
Dim i As Long
If cat.Cells.Count <> val.Cells.Count Then
CountMatchArr = CVErr(xlErrRef)
Exit Function
End If
c = Application.Transpose(cat)
v = Application.Transpose(val)
For i = LBound(c) To UBound(c)
If c(i, 1) = crit Then
If v(i, 1) = c(i, 1) Then
CountMatchArr = CountMatchArr + 1
End If
End If
Next i
End Function
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
Thanks a lot Mr. Rudi
What about range("B1:F1") ?
I need to deal with range("A7:AP8") and range("B1:F1") and the criteria which is in AR7
What about range("B1:F1") ?
I need to deal with range("A7:AP8") and range("B1:F1") and the criteria which is in AR7
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Countif in arrays with two conditions
Sorry...seems I jumped into the middle of this thread with out the full context.
I'll have to read through the thread again.
I'll have to read through the thread again.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Countif in arrays with two conditions
What is the point of using arrays here? It will make the code more complicated...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
Mr. Hans the idea is that this will be used about 10 times in a sheet and then over 100 sheets
The file is so large and no place for formulas, so you find most of my issues are about arrays and dealing with arrays
Thanks for reply
The file is so large and no place for formulas, so you find most of my issues are about arrays and dealing with arrays
Thanks for reply
-
- Administrator
- Posts: 78789
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Countif in arrays with two conditions
Can you use this?
Example of usage:
CountMatch2 Range("B7:AP7"), Range("B1:F1"), Range("B8:AP8"), Range("AR7:AV8")
Code: Select all
Sub CountMatch2(range1 As Range, matchrange As Range, range2 As Range, destrange As Range)
Dim arr1
Dim arr2
Dim arrm
Dim i As Long
Dim j As Long
Dim n As Long
Dim CountInArray As Long
n = destrange.Columns.Count
ReDim temp(1 To 2, 1 To n)
Application.ScreenUpdating = False
arr1 = range1.Value
arr2 = range2.Value
arrm = matchrange.Value
For i = 0 To n - 1
CountInArray = 0
temp(1, n - i) = i
For j = LBound(arr1, 2) To UBound(arr1, 2)
If Not IsError(Application.Match(arr1(1, j), arrm, 0)) And arr2(1, j) = i Then
CountInArray = CountInArray + 1
End If
Next j
temp(2, n - i) = CountInArray
Next i
destrange.Value = temp
Application.ScreenUpdating = True
End Sub
CountMatch2 Range("B7:AP7"), Range("B1:F1"), Range("B8:AP8"), Range("AR7:AV8")
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Countif in arrays with two conditions
In fact Mr Hans It is greater than I imagined. Thank you very much for that Genius solution
You are BRILLIANT
Thanks a lot for this wonderful and incredible help
You are BRILLIANT
Thanks a lot for this wonderful and incredible help