CountIf / SumProduct in VB

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

Vacuum Sealed

Hi All

What would be the best approach for the following scenario:

So much to learn, such a large vacuum of space to fill, I still have so much
to learn

I need help constructing the correct syntax

Sub TrafficFlow()

Dim myRng As Range

Set myRng = [F4:F300]

If myRng.Value >= #12:00:00 AM# And myRng.Value < #1:00:00 AM# Then
Range("M5") = Count(myRng)
End If
If myRng.Value >= #1:00:00 AM# And myRng.Value < #2:00:00 AM# Then
Range("M6") = Count(myRng)
End If
'
'
'down to

If myRng.Value >= #11:00:00 PM# And myRng.Value < #12:00:00 AM# Then
Range("M28") = Count(myRng)
End If
End Sub

As always thank you so much for your assistance.

TIA
Mick.
 
Thx for your reply Don

I was tossing up if to use SELECT, but was struggling with the correct way
to get the Count() incorporated into it.

I had
Select Case myRng
myRng.Value >= #11:00:00 PM# And myRng.Value < #12:00:00 AM#
Case Is Range("M28") = Count(myRng)

Hence the need for outside intervention as I am slow to pick up on the many
variations in code structure.

Thx again.
Mick.
 
Don

I tried the Select this way, of course it did not work...

Sub TrafficFlow()

Dim i As Integer

For i = 4 To 300

Select Case Cells(i, 6).Count

Case Is >= #12:00:00 AM# < #1:00:00 AM#: Range("M5") = Cells(i, 6).Count
Case Is >= #1:00:00 AM# < #2:00:00 AM#: Range("M6") = Cells(i, 6).Count
Case Is >= #2:00:00 AM# < #3:00:00 AM#: Range("M7") = Cells(i, 6).Count
Case Is >= #3:00:00 AM# < #4:00:00 AM#: Range("M8") = Cells(i, 6).Count
Case Is >= #4:00:00 AM# < #5:00:00 AM#: Range("M9") = Cells(i, 6).Count
Case Is >= #5:00:00 AM# < #6:00:00 AM#: Range("M10") = Cells(i, 6).Count
Case Is >= #6:00:00 AM# < #7:00:00 AM#: Range("M11") = Cells(i, 6).Count
Case Is >= #7:00:00 AM# < #8:00:00 AM#: Range("M12") = Cells(i, 6).Count
Case Is >= #8:00:00 AM# < #9:00:00 AM#: Range("M13") = Cells(i, 6).Count
Case Is >= #9:00:00 AM# < #10:00:00 AM#: Range("M14") = Cells(i,
6).Count
Case Is >= #10:00:00 AM# < #11:00:00 AM#: Range("M15") = Cells(i,
6).Count
Case Is >= #11:00:00 AM# < #12:00:00 PM#: Range("M16") = Cells(i,
6).Count
Case Is >= #12:00:00 PM# < #1:00:00 PM#: Range("M17") = Cells(i,
6).Count
Case Is >= #1:00:00 PM# < #2:00:00 PM#: Range("M18") = Cells(i, 6).Count
Case Is >= #2:00:00 PM# < #3:00:00 PM#: Range("M19") = Cells(i, 6).Count
Case Is >= #3:00:00 PM# < #4:00:00 PM#: Range("M20") = Cells(i, 6).Count
Case Is >= #4:00:00 PM# < #5:00:00 PM#: Range("M21") = Cells(i, 6).Count
Case Is >= #5:00:00 PM# < #6:00:00 PM#: Range("M22") = Cells(i, 6).Count
Case Is >= #6:00:00 PM# < #7:00:00 PM#: Range("M23") = Cells(i, 6).Count
Case Is >= #7:00:00 PM# < #8:00:00 PM#: Range("M24") = Cells(i, 6).Count
Case Is >= #8:00:00 PM# < #9:00:00 PM#: Range("M25") = Cells(i, 6).Count
Case Is >= #9:00:00 PM# < #10:00:00 PM#: Range("M26") = Cells(i,
6).Count
Case Is >= #10:00:00 PM# < #11:00:00 PM#: Range("M27") = Cells(i,
6).Count
Case Is >= #11:00:00 PM# < #12:00:00 AM#: Range("M28") = Cells(i,
6).Count

End Select

Next

End Sub


Thx again...

Mick.
 
Don

I tried the Select this way, of course it did not work...

Sub TrafficFlow()

Dim i As Integer

For i = 4 To 300

  Select Case Cells(i, 6).Count

    Case Is >= #12:00:00 AM# < #1:00:00 AM#: Range("M5") = Cells(i, 6).Count
    Case Is >= #1:00:00 AM# < #2:00:00 AM#: Range("M6") = Cells(i, 6).Count
    Case Is >= #2:00:00 AM# < #3:00:00 AM#: Range("M7") = Cells(i, 6).Count
    Case Is >= #3:00:00 AM# < #4:00:00 AM#: Range("M8") = Cells(i, 6).Count
    Case Is >= #4:00:00 AM# < #5:00:00 AM#: Range("M9") = Cells(i, 6).Count
    Case Is >= #5:00:00 AM# < #6:00:00 AM#: Range("M10") = Cells(i, 6).Count
    Case Is >= #6:00:00 AM# < #7:00:00 AM#: Range("M11") = Cells(i, 6).Count
    Case Is >= #7:00:00 AM# < #8:00:00 AM#: Range("M12") = Cells(i, 6).Count
    Case Is >= #8:00:00 AM# < #9:00:00 AM#: Range("M13") = Cells(i, 6).Count
    Case Is >= #9:00:00 AM# < #10:00:00 AM#: Range("M14") = Cells(i,
6).Count
    Case Is >= #10:00:00 AM# < #11:00:00 AM#: Range("M15") = Cells(i,
6).Count
    Case Is >= #11:00:00 AM# < #12:00:00 PM#: Range("M16") = Cells(i,
6).Count
    Case Is >= #12:00:00 PM# < #1:00:00 PM#: Range("M17") = Cells(i,
6).Count
    Case Is >= #1:00:00 PM# < #2:00:00 PM#: Range("M18") = Cells(i, 6).Count
    Case Is >= #2:00:00 PM# < #3:00:00 PM#: Range("M19") = Cells(i, 6).Count
    Case Is >= #3:00:00 PM# < #4:00:00 PM#: Range("M20") = Cells(i, 6).Count
    Case Is >= #4:00:00 PM# < #5:00:00 PM#: Range("M21") = Cells(i, 6).Count
    Case Is >= #5:00:00 PM# < #6:00:00 PM#: Range("M22") = Cells(i, 6).Count
    Case Is >= #6:00:00 PM# < #7:00:00 PM#: Range("M23") = Cells(i, 6).Count
    Case Is >= #7:00:00 PM# < #8:00:00 PM#: Range("M24") = Cells(i, 6).Count
    Case Is >= #8:00:00 PM# < #9:00:00 PM#: Range("M25") = Cells(i, 6).Count
    Case Is >= #9:00:00 PM# < #10:00:00 PM#: Range("M26") = Cells(i,
6).Count
    Case Is >= #10:00:00 PM# < #11:00:00 PM#: Range("M27") = Cells(i,
6).Count
    Case Is >= #11:00:00 PM# < #12:00:00 AM#: Range("M28") = Cells(i,
6).Count

End Select

Next

End Sub

Thx again...

Mick.
Click to expand...

Or without vba, just modify this formula to your needs. Sumproduct can
not use entire columns
Or, send me your file to (e-mail address removed)

=SUMPRODUCT((B1:B21>=1)*(B1:B21<10))
 
What would be the best approach for the following scenario: [....]
Sub TrafficFlow()
Dim myRng As Range
Set myRng = [F4:F300]
If myRng.Value >= #12:00:00 AM# And _
myRng.Value < #1:00:00 AM# _
Then Range("M5") = Count(myRng)
End If
If myRng.Value >= #1:00:00 AM# And _
myRng.Value < #2:00:00 AM# _
Then Range("M6") = Count(myRng)
End If
Click to expand...

Sub TrafficFlow()
Dim myRng As Range, c as Range
Set myRng = Range("F4:F300")
For Each c in myRng
Range("M" & 5+Hour(c)) = myRng.Count
Next

In a later posting, you used cells(6,i).Count. That makes little
sense since the Count is always 1 in that context.
 
Also Don

I tried it this way.

For i = 4 To 300

With Application
.ScreenUpdating = False
End With

Select Case Cells(i, 6).SpecialCells(xlCellTypeConstants).Count

Case #12:00:01 AM# To #1:00:00 AM#
Range("M5") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #1:00:01 AM# To #2:00:00 AM#
Range("M6") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #2:00:01 AM# To #3:00:00 AM#
Range("M7") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #3:00:01 AM# To #4:00:00 AM#
Range("M8") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #4:00:01 AM# To #5:00:00 AM#
Range("M9") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #5:00:01 AM# To #6:00:00 AM#
Range("M10") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count
Case #6:00:01 AM# To #7:00:00 AM#
Range("M11") = Cells(i,
6).SpecialCells(xlCellTypeConstants).Count

End Select

No luck either

Thx again
Mick.
 
Don

Tried your suggestion

=SUMPRODUCT(($F$4:$F$300>#12:00:01#)*($F$4:$F$300,<#1:00:01#))

No good.

Thx agian.
Mick.
 
Thx Joe

Your're right about your assumption of not making sense...

Much of what I submit here makes little sense to me as everyday is a
learning day for me here, although some things take longer to mash into the
gray matter.

will this also then move down the target range of M as each time frame count
value is recorded in a different cell in "M"

eg

upto 1.00am.Value.Count = "M5"
upto 2.00am.Value.Count = "M6"

and so forth to "M28"

Thx again
Mick.
 
Sorry Joe

I tried your code and it returned a value of 297 in each of the target
cells, which isn't quite right as only aroun 50 cells are populated for
testing.

Appreciated the assist.
Mick.
 
Don

Tried your suggestion

=SUMPRODUCT(($F$4:$F$300>#12:00:01#)*($F$4:$F$300,<#1:00:01#))

No good.

Thx agian.
Mick.
Click to expand...

Where e20 & e21 are also times

'=SUMPRODUCT((F4:F400>=E20)*(F4:F400<E21))
 
Your're right about your assumption of not making sense...
Much of what I submit here makes little sense to me as
everyday is a learning day for me here
Click to expand...

No need to get defensive. My comment was not meant as criticism. I
was trying to point the meaning of the Count property.

And on second thought, I wonder if you do not mean to use Count at
all. It returns the number of cells in a range. So myRng.Count is
297 with your range of F4:F300.

I wonder if you want is:

For each c in myRng
Range("M" & 5+Hour(c)) = Range("M" & 5+Hour(c)) + 1
Next
 
Thx again Don.

Your code modded.

=SUMPRODUCT(($F$4:$F$300>=$O5)*($F$4:$F$3400<$P5))

eg Column "O" = 00:01 & Column "P" = 01:00

This returned N/A#

Regards
Mick.
 
Sorry Joe

That halted on:

Range("M" & 5+Hour(c)) = Range("M" & 5+Hour(c)) + 1

Regards
Mick.
 
Sorry Joe
That halted on:
    Range("M" & 5+Hour(c)) = Range("M" & 5+Hour(c)) + 1
Click to expand...

Works fine for me. I don't know how much spoon-feeding you need.
Copy and paste the following:

Sub TrafficFlow()
Dim myRng As Range, c As Range
Set myRng = Range("F4:F300")
For Each c In myRng
Range("M" & 5 + Hour(c)) = Range("M" & 5 + Hour(c)) + 1
Next
End Sub

Now, you never explained what you want the code to do. Something
like: "F4:F300 contains the times of individual observations. In
M5:M28, I want to count the number of observations in each hour of the
day".

That is what I assume by the code above. There are better was to
implement it. I tried to stray as little from your original
implementation as possible initially.

If you want to avoid VBA altogether, put the following formula into M5
and copy down through M28:

=SUMPRODUCT(--(HOUR($F$4:$F$300)=ROWS($M$5:M5)-1))

Be sure to copy-and-paste the formula.
 
Works fine for me.  I don't know how much spoon-feeding you need.
Copy and paste the following:

Sub TrafficFlow()
Dim myRng As Range, c As Range
Set myRng = Range("F4:F300")
For Each c In myRng
   Range("M" & 5 + Hour(c)) = Range("M" & 5 + Hour(c)) + 1
Next
End Sub

Now, you never explained what you want the code to do.  Something
like:  "F4:F300 contains the times of individual observations.  In
M5:M28, I want to count the number of observations in each hour of the
day".

That is what I assume by the code above.  There are better was to
implement it.  I tried to stray as little from your original
implementation as possible initially.

If you want to avoid VBA altogether, put the following formula into M5
and copy down through M28:

=SUMPRODUCT(--(HOUR($F$4:$F$300)=ROWS($M$5:M5)-1))

Be sure to copy-and-paste the formula.
Click to expand...
 
Errata...

If you want to avoid VBA altogether, put the following
formula into M5 and copy down through M28:
=SUMPRODUCT(--(HOUR($F$4:$F$300)=ROWS($M$5:M5)-1))
Click to expand...

Since you said that F4:F300 might not be fully populated, the
SUMPRODUCT formula should be:

=SUMPRODUCT(($F$4:$F$300<>"")*(HOUR($F$4:$F$300)=ROWS($M$5:M5)-1))

I wrote regarding a VBA implementation:
There are better [ways] to implement it.
Click to expand...

Here is one:

Sub countTimes()
'*** modify Consts as needed
Const srcRng As String = "f4" 'should be 1 cell
Const destRng As String = "m5:m28" 'should be 24 cells
'*** hr is two-dimensional to make it
'*** easier to write into Range(destRng)
Dim hr(0 To 23, 1 To 1) As Long, i As Long, x As Long
Dim data As Variant
'*** assume contiguous data starting in srcRng
If Not IsEmpty(Range(srcRng)) Then
'*** create data(1 to n, 1 to 1) as Variant
data = Range(srcRng, Range(srcRng).End(xlDown))
For i = 1 To UBound(data, 1)
x = Hour(data(i, 1))
hr(x, 1) = hr(x, 1) + 1
Next
End If
Range(destRng) = hr
End Sub
 
PS....

you never explained what you want the code to do.  Something
like:  "F4:F300 contains the times of individual observations.
 In M5:M28, I want to count the number of observations in each
hour of the day".
Click to expand...

I was thrown off by your use of the Count property. I neglected to
put everything in the context of your subject line. Always best to
"duplicate" (really expand on) the subject in the body of your
posting.

For future note, one way to learn what a particular property or method
does is: if you write in a proper context as you did in some of your
posting, e.g. cells(i,6).count, you can put the mouse cursor over the
word "count", then press F1 to see the help page.

I think then you might have seen that it was not the type of counting
that you needed.

Arguably, VBA help pages are not always so helpful; typical of
Microsoft documentation, IMHO. But I wanted to explain how to use F1
for the future.
 
You are so right Joe

I have much to learn, and continue to do so with the valuable assistance I
get here from generous people like yourself who give freely of their
knowledge and experience...

I will try and put more emphasis on what it is I need along with expected
outcome, without dragging it out into an epic....

BTW:

Your nested formula worked nicely.

The VB one did not as it returned a value of just "3" in two of the cells.

I will settle for the nested and forego the VB as I have to move onto
another project.

Once again

A huge thx to both Don & yourself for your patience with me.

Mick
 
And again

Sorry Joe

I neglected to increase the range in srcRng to "F4:F300", interestingly the
first cell "M5" had a count value of 177, yet the balance of the cells below
all displayed the correct count.

All the same, I will stick with the nested formula.

Cheers
Mick.
 
Back
Top