Solved: Count if on VBA

[Ismael]

Hi to all,

In file attach, you guys will see that a have created a macro to type in column M all the unique values that are in column B and C.

Ok so far so good, but I need to do a few changes on this, but I'm having same difficulties on doing what I want.

So what I pretend now in count how many times I have the value LO for the flight AZ 21, and how many values TR I have for the flight AZ 21.

The flights are typed by the macro on column M. I think that its something like a countif function. And then do the same thing for the others flights.

After this I want to now also what are the largest value (maximum) on column H for each flight.

So if any of you guys can help me, I really appreciate.

best regards,

Ismael

[XLGibbs]

Based on the layout of the data and your criteria specified..the SUMPRODUCT variation for multiple criteria would work....

variations of the formula can be used to use cell references as criteria...

this returns 27....

=SUMPRODUCT(--($B$4:$B$599 = "AZ 21"),--($D$4:$D$599 = "LO"))

This returns 23

=SUMPRODUCT(--($B$4:$B$599 = "AZ 21"),--($D$4:$D$599 = "TR"))

Attached is sample with aggregates next to column M using cell references...

[Norie]

Are you looking for a formula or code?

This formula placed in N4 and copied down/across appears to work for your first question.

=SUMPRODUCT(($B$4:$B$599=$M4)*($D$4:$D$599=N$3))

[Norie]

Gibbs

Perhaps I'm missing something, but I can't see any formulas in your attachment.:)

[XLGibbs]

Oops. attahced his original instead of my zip file, which did include the formula you mention above...

Thanks for noticing that Norie

[Ismael]

Hi Gibbs,

Your formula does exactly what I want, but isn't possible do this on VBA code?

And How can I solve the problem of the maximum value (column H) for each flight?

Thanks.

best regards,

Ismael

[matthewspatrick]

I got the max values by using this array formula in N4 and copying down:

{=MAX(IF($B$4:$B$599=M4,$H$4:$H$599))}

Of course, to do that in code you must use R1C1 notation...

[Norie]

Ismael

Yes it is possible to add this to your existing code.

That's why I asked if you wanted formula or code.:)

[Ismael]

yes, its exactly this what I want (see yeloo range on file attach), but on VBA, because I will do this on several files, and same files have 1000 rows of data and others have 10000 for example.

So, if I do this by formulas, I will have to copy down the formula on each file, and the calculation will stay very slow. On vba I think that this will be more faster.

It's possible for you guys give me a help on this?

Best regards, and thanks for the help.

Ismael

[Ismael]

Hi,

I think that I'm close to a possible solution.

In attach file you guys will find a macro "count" that return's a message if to conditions are true. What I pretend is instead of return the message, return how many times the condition is true.

And then do the same thing to the flight placed on M5, M6, etc... (this is done by the macro fcnContar).

So if any of you guys can help me, I really appreciate.

Best regards,

Ismael

[Bob Phillips]

Sub contar()
Dim UltLin As Long
Dim num As Long
Dim stemp As String
UltLin = Range("H" & Rows.Count).End(xlUp).Row
For i = 4 To Range("m" & Rows.Count).End(xlUp).Row
num = Evaluate("=SUMPRODUCT(--(B4:B" & UltLin & "=M" & i & _
"),--(D4:D" & UltLin & "=""LO""))")
stemp = stemp & Range("M" & i).Value & " occurs " & num & " times" & vbNewLine
Next
MsgBox stemp
Columns("M:M").HorizontalAlignment = xlLeft
End Sub

[Ismael]

Hi Xld,

In first place thanks for the help.
But isn't possible instead of type the information of "stemp" on msg type this information on N4, N5, N6, ect.

Something like that:

On M4 I have AZ 21
On N4 I will have 3
On M5 I have AZ 27
On N4 I will have 14

And do the same to the others fligths.

Best regards,

Ismael

[Bob Phillips]

Sub contar()
Dim UltLin As Long
Dim num As Long
Dim stemp As String
UltLin = Range("H" & Rows.Count).End(xlUp).Row
For i = 4 To Range("m" & Rows.Count).End(xlUp).Row
num = Evaluate("=SUMPRODUCT(--(B4:B" & UltLin & "=M" & i & _
"),--(D4:D" & UltLin & "=""LO""))")
Range("N" & i).Value = num
Next
Columns("M:M").HorizontalAlignment = xlLeft
End Sub

[Ismael]

Hi XLD,

Thanks it's perfect.

I already change the macro to this:

Sub contar_LO()
Dim UltLin As Long
Dim num As Long
Dim stemp As String
UltLin = Range("H" & Rows.Count).End(xlUp).Row
For i = 4 To Range("m" & Rows.Count).End(xlUp).Row
num = Evaluate("=SUMPRODUCT(--(B4:B" & UltLin & "=M" & i & _
"),--(D4:D" & UltLin & "=""LO""))")
Range("N" & i).Value = num
Next
Columns("M:M").HorizontalAlignment = xlLeft
UltLin = Range("H" & Rows.Count).End(xlUp).Row
For i = 4 To Range("m" & Rows.Count).End(xlUp).Row
num = Evaluate("=SUMPRODUCT(--(B4:B" & UltLin & "=M" & i & _
"),--(D4:D" & UltLin & "=""TR""))")
Range("O" & i).Value = num
Next
Columns("M:M").HorizontalAlignment = xlLeft
End Sub

The blue part is what I change. Now I have the count of "LO" on column M and "TR" on Column N.

To this stay perfect I only need to add one thing that is the maximum value of column H. (see file attach)

Can you help me with this?

Best regards,

Ismael

[Bob Phillips]

Not sure what you want to do with it, but you can get it like so

MsgBox Format(Application.Max(Range("H4:H" & UltLin)), "hh:mm:ss")

BTW, still using that Flight Allocation application?

[Ismael]

Hi again,

Yes, I'm still using the flight allocation application, this as been very useful to me in the last times you now. Because with that application I was able to demostrate that the IATA (one organization of airports) was wrong on one study of flight allocation on Lisbon Airport.

Thanks once more.

But let see I think you don't full understand my doubt. the piece of code that you send' me, give the maximum of column H. But I don't want that.

What I pretend is to now for each flight what are the maximum of column H, in order to now what was the last baggage that enter in the system of each flight.

So in the last file that I attach, this is represented with the array formula on column P

={MAX(IF(($B$4:$B$599=M4)*($D$4:$D$599="LO");$H$4:$H$599))}

but I would like if possible have this on VBA, in order to run the macro and everything will be calculated.

In resume, at the end I will put all the code in one macro in order to make all this at once. Don't you think that this is a good idea?

Best regards,

Ismael

[Bob Phillips]

Hi again,

Yes, I'm still using the flight allocation application, this as been very useful to me in the last times you now. Because with that application I was able to demostrate that the IATA (one organization of airports) was wrong on one study of flight allocation on Lisbon Airport.

Thanks once more.

But let see I think you don't full understand my doubt. the piece of code that you send' me, give the maximum of column H. But I don't want that.

What I pretend is to now for each flight what are the maximum of column H, in order to now what was the last baggage that enter in the system of each flight.

So in the last file that I attach, this is represented with the array formula on column P

={MAX(IF(($B$4:$B$599=M4)*($D$4:$D$599="LO");$H$4:$H$599))}

but I would like if possible have this on VBA, in order to run the macro and everything will be calculated.

In resume, at the end I will put all the code in one macro in order to make all this at once. Don't you think that this is a good idea?

Best regards,

Ismael

Yep.

[Ismael]

Good Morning Bob,

Thanks for the help. The file is perfect.
Once more (I shoudl say always) you give me a precious help.

Best regards,

Ismael

[Bob Phillips]

Good Morning Bob,

Thanks for the help. The file is perfect.
Once more (I shoudl say always) you give me a precious help.

Best regards,

Ismael

I'm in Seville in a few weeks, not too far away, you can buy me a virtual beer http://vbaexpress.com/forum/images/smilies/001.gif

[Ismael]

Ok Bob,
no problem :beerchug: , normally were we said that we have a good beer.

I alreday was in seville, it's a good town, you will enjoy.

Best regards, :friends:

Ismael