PDA

View Full Version : Solved: count all equal combinations



Raf
10-02-2009, 07:23 AM
Hi there,

I'm struggling to get a code which will count all equal combinations that are listed in range a1:b18. Range c1:g5 is a table which displays the results. I did manage to write a code that counts the amount of the first combination. But I would like to get more variables in my code, so I don't need to write a new code for each unique combination.
In the attachment is an example of what I need. All bold and italic values I put in my self. They are just to show what the result should be. The actual file will be much bigger. Also will there be names in stead of nrs and letters. I must use a vba code only.

Here's the code I wrote (it's also in the attachment).

Sub test()
Dim c As Range
Dim d As Range
Dim Number As Integer
Number = 0
For Each c In Range("A1:b18")
If c.Value = Range("c2") And c.Offset(0, 1).Value = Range("d1") Then
Number = Number + 1
End If
Next
Range("d2") = Number
End Sub

Thanks in advance,

Raf.

georgiboy
10-02-2009, 11:10 AM
I know you say you want this in vba, but why recreate the wheel when you have pivot tables built into excel.

Raf
10-05-2009, 03:55 AM
Thanks for the tip georgiboy.
I never worked with this tool before, and I must say it's a brilliant one!
But it didn't work for me, so after a view days working on it, I returned to the vba and finally found the right code. You can see it in my attached file.

slamet Harto
10-05-2009, 08:54 PM
another approach
=SUMPRODUCT(($C2=$A$1:$A$18)*(D$1=$B$1:$B$18))