VBA countif based on multiple criteria - paste to a summary table in another sheet

**sloshpuppy** · 04-26-2018, 06:20 AM

Hi,

This is driving me crazy - please help !!!!

I want to execute a Countif in VBA based on multiple criteria then paste the information into a summary table in another sheet.

for example: - in my workbook I have two tabs "Sheet 1" is called Import, "Sheet 2" is called Summary.

Ideally I want to paste a mass data set into the Import tab (columns A:Q) row 1 is the header. The data set can vary in number of rows for example, one day it could be 600 rows another day it could be 300 and so on. Basically its not a fixed range however, the data I am counting will always be in column A - if that makes sense?

Executing the VBA will count down column A to the last row in the Import Sheet based on the criteria I set and then paste the results into a table in the Summary sheet. The table has headings corresponding to what the Countif is counting which is always static.

The process is repeated everyday - so the criteria being counted needs to paste in the next available row in the table located in the summary sheet.

I have created a mock up workbook using countries as the imported data set to kind help with what i am trying to achieve.

Any help or pointers will be a massive help

I've tried google and managed to find this but its too static, I am unsure how to manipulate this to achieve my goal?

Countif2()

Range("F2").Value = Application.WorksheetFunction.CountIf(Range("a2:a800"), "USA")

End Sub

Best wishes

Slosh

**AlphaFrog** · 04-26-2018, 06:59 AM

Sub Countif2()
    
    Dim rng As Range
    Set rng = Sheets("Import").Range("A:A")
    
    With Sheets("Summary").ListObjects(1).ListRows.Add.Range
    
        .Cells(1, "A").Value = Date
    
        .Cells(1, "B").Value = Application.CountIf(rng, "USA") + _
                               Application.CountIf(rng, "UK") + _
                               Application.CountIf(rng, "FRANCE")
                             
        .Cells(1, "C").Value = Application.CountIf(rng, "JAPAN")
        
        .Cells(1, "D").Value = Application.CountIf(rng, "CHINA")
        
        .Cells(1, "E").Value = Application.CountIf(rng, "KOREA")
        
        .Cells(1, "F").Value = Application.CountIf(rng, "HOLAND")
        
        .Cells(1, "G").Value = Application.CountIf(rng, "SPAIN") + _
                               Application.CountIf(rng, "PORTUGAL")
                             
        .Cells(1, "H").Value = Application.CountIf(rng, "POLAND")
        
    End With
    
End Sub

**sloshpuppy** · 04-26-2018, 07:08 AM

Absolutely bloody brilliant AlphaFrog

Power of the people

Much appreciated

Slosh

**AlphaFrog** · 04-26-2018, 07:15 AM

You're welcome. Thanks for the feedback.

I tweaked it a bit to make it a little more concise. Essentially it's the same code. See the post above.

VBA countif based on multiple criteria - paste to a summary table in another sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA countif based on multiple criteria - paste to a summary table in another sheet

Re: VBA countif based on multiple criteria - paste to a summary table in another sheet

Re: VBA countif based on multiple criteria - paste to a summary table in another sheet

Re: VBA countif based on multiple criteria - paste to a summary table in another sheet

Thread Information

Users Browsing this Thread

Similar Threads

copying column headers from multiple sheets into a "summary" sheet, based on criteria

[SOLVED] Copy and paste specific cell to another sheet based on multiple criteria

Create a summary sheet for multiple worksheets based on multiple criteria with formulas

Generating a data summary sheet based on multiple criteria

Create criteria-based summary table from table rows across sheets

Copy data from one sheet and paste to another based on multiple criteria

Copy\Paste multiple criteria based rows to new sheet

Bookmarks

Bookmarks

Posting Permissions