Excel VBA - combining column information

[royend]

Hi,

I am making an excel document which has several tables and all kinds o
functions, but there is one problem I just cannot solve:
Issue: I have two columns with parallell information, meaning that th
information has a connection. Let's call one "Name" and the othe
"Age". What I would like to do is count how many times a certain valu
of age AND name appears, for example "Hans" as Name and "22" as age.
This is what I tryed:
COUNTIF((B7:B500,"Hans")AND(C7:C500,22))
I also tryed:
COUNT(OR(B7:B500="Hans,C7:C500=22)) 'Suppose to count where bot
expressions are TRUE.

In addition I have tryed several other more or less comprehensiv
functions, but none have worked.

I have much experience with ASP-scripting, and in ASP (VB) it woul
look like this:
counter = 0
While Not Name.EOF OR Age.EOF
If Name = Hans Then
If Age = 22 Then
counter = counter + 1
End If
End If
Wend

May you help me on this one?? Is there any easy solutions?

Best Regards.

Roy Endré Dah

 

[Frank Kabel]

Hi
try a non VBA solution
=SUMPRODUCT(--(B7:B500="Hans"),--(C7:C500=22))

 

[Tom Ogilvy]

=sumproduct(--(B7:B500="Hans"),--(C7:C500=22))

 

[Kevin Beckham]

Put the following code in a Visual Basic code sheet

Function CountOccurrences(rng_1 As Range, target_1, _
rng_2 As Range, target_2) As Long
Dim iCount As Long
Dim rowCount As Long
Dim rowIndex As Long

rowCount = Application.WorksheetFunction _
.Min(rng_1.Rows.Count, rng_2.Rows.Count)

iCount = 0
For rowIndex = 0 To rowCount - 1
If rng_1.Resize(1, 1).Offset(rowIndex, 0).Value =
target_1 And _
rng_2.Resize(1, 1).Offset(rowIndex, 0).Value =
target_2 Then
iCount = iCount + 1
End If
Next rowIndex

CountOccurrences = iCount
End Function

and in a cell put
=CountOccurrences(B7:B500, "Hans", C7:C500, 22)

Kevin Beckham

 

[royend]

Thanks!

I will try this at once!

Roy Endr

 

[royend]

The non-VBA solutions did not work. (Maybe because I just didn'
understand what to put instead of --, I tryed different settings
though).

The VBA-solution is basically what I thought would work, but it seem
to be some weird bugs into the whole code. I have "deschiffered" i
and this is my result:

Function CountOccurrences(rng_1 As Range, target_1, _
rng_2 As Range, target_2) As Long
Dim iCount As Long
Dim rowCount As Long
Dim rowIndex As Long

rowCount = Application.WorksheetFunction _
.Min(rng_1.Rows.Count, rng_2.Rows.Count)
iCount = 0
For rowIndex = 0 To rowCount - 1
If rng_1.Resize(1, 1).Offset(rowIndex, 0).Value = target_1 An
rng_2.Resize(1, 1).Offset(rowIndex, 0).Value = target_2 Then
iCount = iCount + 1
End If
Next rowIndex
CountOccurrences = iCount
End Function

-----
I have taken all the 3d and 20s away - didn't know what their purpos
was.

Thanks for every tips in advance.

Roy Endr

 

[Frank Kabel]

Hi
just type the '--'. It's a double minus used to convert the boolean
value to a number. Just copy the formulas (Tom's or mine) to your
worksheet. Both should do

 

[royend]

I believed it to be some bolean expression, but it didn't work.. it gav
me the result: #VALUE!

Maybe it is because I jungle between worksheets:
=SUMPRODUCT((--('01-Sheet2'!B7:B500=Avd!A3),--('01-Sheet2'!C7:C500<100))

It works when I use the function in the same worksheet that contain
the data.

Well.... it's an odd thing this.

Thanks for all your help and time!

New hints and tips will be appreciated

Roy Endr

 

[Frank Kabel]

Hi
no this should work. What's the value in Avd!A3?

 

[royend]

The value in Avd!A3 is a text: "hans".

The funny thing is that it worked when I tryed the same function in th
same sheet as contains the data. Then it returned the correct result.

I have made a way around the problem, so I consider this matter a
solved.

Thanks for all your help. Ia m truly thankful!

Best Regards.
Roy Endr

 

[Tom Ogilvy]

may be an email typo, but your parentheses are unbalanced

=SUMPRODUCT(--('01-Sheet2'!$B$7:$B$500=Avd!A3),--('01-Sheet2'!$C$7:$C$500<10
0))

do you have errors in the source data?

 

[royend]

Hi,

There is no error in my source data. The last 490 are blanks, but tha
should not affect the function.

roy endr