Code to Count specific occurances in a sheet?

[NessPJ]

Hi all,

I'm looking for a piece of code that will count the number of specified occurances in one of my sheets.

I hope the following explanation will suffice.....

My sheet contains a lot of data like:
Column A: Date
Column B: Time
Column C: Error code
Column D: A formula to show a "1" if a specific error code occurs between 0:00 and 08:00u.
Column E: A formula to show a "1" if a specific error code occurs between 8:01 and 16:00u.

Now, what i want to do is...count the number of 1's in column D and E for a specific Date.
This date is retreived from another cell in the workbook.

 

[Gerald Higgins]

Let's assume you specify your date in cell H1.

=SUMPRODUCT(--(A1:A11=H1),--(D1:D11=1))

will do Col D.
Repeat for Col E, then add the results.
You can do this all in one cell

=sumproduct(...1)+sumproduct(...2)

 

[NessPJ]

Hey there,

Thanks for the quick reply!
Though i'm not exactly sure if Sumproduct is going to help me on this one.

I uploaded an example sheet here if you wish to take a look...

Column A contains a variety of dates going from 03-04-11 to 12-05-11 for example.
Columns R to AG are the ones with the formulae for counting error codes.

 

[Gerald Higgins]

Are you saying that you want to count EVERY column from R to AG inclusive ?
That's quite different to what you said in the OP

Something that may make this simpler - what are the other possible values of these columns, other than 1 ?
Is the only other possible value blank or zero ?
Or could they also be 2, 3, 1.5 or whatever ?

 

[NessPJ]

Hey,

Basically what columns R to AG do is show if error code 290 appears for a specified location (4 of them) at a specified time window.
There are 4 locations and 4 time windows resulting in (4x4) 16 columns total.

In detail...for column R, the cells will show value "1" if error code 290 appears for section 521 in a time window between 23:00h and 23:59h.
In any other case the cell will show value "0".

Now the solution i am looking for is for something giving me these numbers per date.
So for example...a piece of code that will count for Column R how many 1's there are when the date in Column A equals a different cell in my workbook (take 05-04-2011 for example).

The reason i want to do this with a piece of code or macro, is because the sheet you see is generated by a macro as well (the length and data of the sheet aren't static, but updated on a regular basis).

 

[Gerald Higgins]

Sorry, I missed the requirement to do this through code.

You could still use sumproduct, by specifying a range that was comfortably larger than the largest range you expect.
For example, if the number of rows hovers around, say, 300, then specify 1,000, or 2,000.

I'm afraid I don't follow the logic of the rest of your last post, sorry

 

[NessPJ]

Perhaps it would be easier if i requested, looking for a piece of code/macro that will copy all rows that contain a specific date (specified in some cell) to a specified sheet?
This would in turn allow me to use a less complicated piece of code to just count every column seperately on the target worksheet.

 

[Jameo]

So let me get this right.

You already have the formula to populate a "1" in the error code columns. What you wish is a macro to count the amount of times a "1" appears in each of the error columns? At the bottom of each of the error columns that change dependant on the amount of data generated in the sheet?

Let me know if this is right, if it is I'll write you a small bit of code.

 

[Jameo]

If I have the above correct, then this will work for you.

Sub CountError()
    Dim lRow, nRow As Long
Dim Er1, Er2, Er3, Er4, Er5, Er6, Er7, Er8, Er9, Er10, Er11, Er12, Er13, Er14, Er15, Er16 As Long
 
Er1 = 0
Er2 = 0
Er3 = 0
Er4 = 0
Er5 = 0
Er6 = 0
Er7 = 0
Er8 = 0
Er9 = 0
Er10 = 0
Er11 = 0
Er12 = 0
Er13 = 0
Er14 = 0
Er15 = 0
Er16 = 0
  
    lRow = Range("A" & Rows.Count).End(xlUp).Row + 1
  
    
 
    For i = 1 To lRow
        If Cells(i, 18).Value = "1" Then
            Er1 = Er1 + 1
        End If
  
        If Cells(i, 19).Value = "1" Then
            Er2 = Er2 + 1
        End If
        
        If Cells(i, 20).Value = "1" Then
            Er3 = Er3 + 1
        End If
        
        If Cells(i, 21).Value = "1" Then
            Er4 = Er4 + 1
        End If
        
        If Cells(i, 22).Value = "1" Then
            Er5 = Er5 + 1
        End If
        
        If Cells(i, 23).Value = "1" Then
            Er6 = Er6 + 1
        End If
        
        If Cells(i, 24).Value = "1" Then
            Er7 = Er7 + 1
        End If
        
        If Cells(i, 25).Value = "1" Then
            Er8 = Er8 + 1
        End If
        
        If Cells(i, 26).Value = "1" Then
            Er9 = Er9 + 1
        End If
        
        If Cells(i, 27).Value = "1" Then
            Er10 = Er10 + 1
        End If
        
        If Cells(i, 28).Value = "1" Then
            Er11 = Er11 + 1
        End If
        
        If Cells(i, 29).Value = "1" Then
            Er12 = Er12 + 1
        End If
        
        If Cells(i, 30).Value = "1" Then
            Er13 = Er13 + 1
        End If
        
        If Cells(i, 31).Value = "1" Then
            Er14 = Er14 + 1
        End If
        
        If Cells(i, 32).Value = "1" Then
            Er15 = Er15 + 1
        End If
        
        If Cells(i, 33).Value = "1" Then
            Er16 = Er16 + 1
        End If
    Next i
 
    Sheets("sheet1").Cells(lRow, 18).Value = Er1
    Sheets("sheet1").Cells(lRow, 19).Value = Er2
    Sheets("sheet1").Cells(lRow, 20).Value = Er3
    Sheets("sheet1").Cells(lRow, 21).Value = Er4
    Sheets("sheet1").Cells(lRow, 22).Value = Er5
    Sheets("sheet1").Cells(lRow, 23).Value = Er6
    Sheets("sheet1").Cells(lRow, 24).Value = Er7
    Sheets("sheet1").Cells(lRow, 25).Value = Er8
    Sheets("sheet1").Cells(lRow, 26).Value = Er9
    Sheets("sheet1").Cells(lRow, 27).Value = Er10
    Sheets("sheet1").Cells(lRow, 28).Value = Er11
    Sheets("sheet1").Cells(lRow, 29).Value = Er12
    Sheets("sheet1").Cells(lRow, 30).Value = Er13
    Sheets("sheet1").Cells(lRow, 31).Value = Er14
    Sheets("sheet1").Cells(lRow, 32).Value = Er15
    Sheets("sheet1").Cells(lRow, 33).Value = Er16
    
Er1 = 0
Er2 = 0
Er3 = 0
Er4 = 0
Er5 = 0
Er6 = 0
Er7 = 0
Er8 = 0
Er9 = 0
Er10 = 0
Er11 = 0
Er12 = 0
Er13 = 0
Er14 = 0
Er15 = 0
Er16 = 0

End Sub

 

[NessPJ]

Hey there,

Thanks a lot for the information.
The only extra argument i was looking for would be, for the code to only count the arguments for a specific date (a date specified in a cell somewhere else in the workbook).