Countif?

C

cccbzg

Board Regular
Joined
Oct 5, 2014
Messages
68
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've got a spread sheet that gets updated with a macro to show a date and code. I need to count up all of the dates for each ID number generating the total field in col D. What complicates things for me is that there are an undetermined number of cols for each ID (row).

Many thanks for any help.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]fname
[/TD]
[TD]lname
[/TD]
[TD]total
[/TD]
[TD]date 1
[/TD]
[TD]code1
[/TD]
[TD]date2
[/TD]
[TD]code2
[/TD]
[TD]date3
[/TD]
[TD]code3
[/TD]
[TD]date4
[/TD]
[TD]code4
[/TD]
[TD]date5
[/TD]
[TD]code5
[/TD]
[TD]date6
[/TD]
[TD]code6
[/TD]
[TD]date7
[/TD]
[TD]code7
[/TD]
[TD]date8
[/TD]
[TD]code8
[/TD]
[/TR]
[TR]
[TD]001
[/TD]
[TD]sam
[/TD]
[TD]smith
[/TD]
[TD]2
[/TD]
[TD]10/10/2015
[/TD]
[TD]VM
[/TD]
[TD]10/11/2015
[/TD]
[TD]HP
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]002
[/TD]
[TD]john
[/TD]
[TD]doe
[/TD]
[TD]1
[/TD]
[TD]09/01/2015
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003
[/TD]
[TD]ben
[/TD]
[TD]franklin
[/TD]
[TD]3
[/TD]
[TD]09/02/2015
[/TD]
[TD]hist
[/TD]
[TD]09/08/2015
[/TD]
[TD]rre
[/TD]
[TD]09/12/2015
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004
[/TD]
[TD]tom
[/TD]
[TD]jefferson
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
since countif ignores blanks and text in the range argument you could just use something like

=COUNTIF(E2:BA2,">0")

I'd just make the range some obscenely high number to make sure you capture all the columns

i guess one caveat to this would be if any of the codes are numbers....
 
Upvote 0
This code will fill column D ("total") with the desired results:

Code: 
Sub countThings()


Dim lastRow, thisDate, datesFound, rowCounter, colCounter As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row


    Range("D2:D" & lastRow).ClearContents
    
    For rowCounter = 2 To lastRow
    
            Dim lastColumn As Long
                lastColumn = Cells(rowCounter, Columns.Count).End(xlToLeft).Column
                datesFound = 0
                
        For colCounter = 5 To lastColumn Step 2
        
            If IsDate(Cells(rowCounter, colCounter)) Then
                datesFound = datesFound + 1
            End If
        
        Next colCounter
        
        Cells(rowCounter, 4).Value = datesFound
        
    Next rowCounter


MsgBox "complete"


End Sub
 
Upvote 0
Weazel said:
since countif ignores blanks and text in the range argument you could just use something like

=COUNTIF(E2:BA2,">0")

I'd just make the range some obscenely high number to make sure you capture all the columns

i guess one caveat to this would be if any of the codes are numbers....
Click to expand...

Ha, much simpler and easier than what I offered. I'm just on here to practice my VBA skills. You could just use the formula cccbzg provided!
 
Upvote 0
thanks for the like Sven,

I like your code much better than the countif since it doesn't seem to matter if the code 1, code 2, etc. are text or numbers.

I really don't know VBA much so I'll have to dissect what you wrote.

very nice.
 
Upvote 0
Here is the same code with some added comments for you (and OP) to understand what is going on

Code: 
Sub countThings()


Dim lastRow, thisDate, datesFound, rowCounter, colCounter As Long
    
    'find the last row used
    lastRow = Range("A" & Rows.Count).End(xlUp).Row


    'clear column D
    Range("D2:D" & lastRow).ClearContents
    
    'for-next loop to go through each row
    For rowCounter = 2 To lastRow
    
            Dim lastColumn As Long
            
                'find last column used for each used row (each person, basically)
                lastColumn = Cells(rowCounter, Columns.Count).End(xlToLeft).Column
                
                'start with 0
                datesFound = 0
                
        'for-next loop to go to the right through the columns
        For colCounter = 5 To lastColumn Step 2 'step 2 because every 2 columns is the date category
            
            'check if the value is a date
            If IsDate(Cells(rowCounter, colCounter)) Then
            
                'if so, add one to our datesFound variable
                datesFound = datesFound + 1
            End If
        
        'now go back up to the top of this loop, where it will add 2 to colCounter
        Next colCounter
        
        'once we've counted all the columns in that row that are dates, put that number in column D
        Cells(rowCounter, 4).Value = datesFound
        
    'go to the next row and do it all again
    Next rowCounter


MsgBox "complete"


End Sub

I know it can look daunting before you start learning VBA but I knew nothing about this a few months ago! A really great way to learn the basics is to watch WiseOwlTutorials on YouTube... he's really good at explaining this stuff. Here is a link: https://www.youtube.com/playlist?list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5
 
Upvote 0
svendiamond said:
Here is the same code with some added comments for you (and OP) to understand what is going on

Code: 
Sub countThings()


Dim lastRow, thisDate, datesFound, rowCounter, colCounter As Long
    
    'find the last row used
    lastRow = Range("A" & Rows.Count).End(xlUp).Row


    'clear column D
    Range("D2:D" & lastRow).ClearContents
    
    'for-next loop to go through each row
    For rowCounter = 2 To lastRow
    
            Dim lastColumn As Long
            
                'find last column used for each used row (each person, basically)
                lastColumn = Cells(rowCounter, Columns.Count).End(xlToLeft).Column
                
                'start with 0
                datesFound = 0
                
        'for-next loop to go to the right through the columns
        For colCounter = 5 To lastColumn Step 2 'step 2 because every 2 columns is the date category
            
            'check if the value is a date
            If IsDate(Cells(rowCounter, colCounter)) Then
            
                'if so, add one to our datesFound variable
                datesFound = datesFound + 1
            End If
        
        'now go back up to the top of this loop, where it will add 2 to colCounter
        Next colCounter
        
        'once we've counted all the columns in that row that are dates, put that number in column D
        Cells(rowCounter, 4).Value = datesFound
        
    'go to the next row and do it all again
    Next rowCounter


MsgBox "complete"


End Sub

I know it can look daunting before you start learning VBA but I knew nothing about this a few months ago! A really great way to learn the basics is to watch WiseOwlTutorials on YouTube... he's really good at explaining this stuff. Here is a link: https://www.youtube.com/playlist?list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5
Click to expand...

Thanks SO very much!! This works great!
 
Upvote 0
I love this approach...how simple!! Thanks very much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Issue rearranging/sorting data based on multiple columns
Replies
1
Views
413
BasicContract8989
B
L
  • Solved
Consolidating Stock Backtest Data into Table
Replies
1
Views
412
LakeDog
L
K
  • Solved
EXCEL Formula Help Required
Replies
7
Views
404
GraH
GraH
dss28
  • Question Question
Change one or multiple cell values in a row and reflect the same in diffenent sheets in the same workbook
Replies
4
Views
625
JEC
JEC
B
  • Solved
Clear Row Data based on Date Range
Replies
3
Views
420
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top