VBA Sum CounIf formulas and copy formula down

smapple

New Member
Joined
Jan 15, 2018
Messages
6
Hello,

I'm sure this is an easy one, but I've scoured the web and can't quite find what I'm after. I have a dataset that I pull monthly that contains a new record in each row. I would like to write a macro that adds up the number of "Yes" and "N/A" answers for each record and divides by 25 (the number of questions). I currently do this without VBA by computing:
=(countif(F2:AD2, "Yes")+countif(F2:AD2, "N/A"))/25 and copying down, however, I'm in the process of automating this work a bit more. Each time I pull this data, there is a different number of rows, but the columns will be the same.

I tried the below, but I'm sure you can see that this computes the 'Pass Rate' for the first record and then copies that same value all the way down. Any help you can offer me would be very appreciated.

' Calculate % Pass Rate
Dim YesAns As Long
Dim NAAns As Long
Dim LastRow As Long

YesAns = Application.WorksheetFunction.CountIf(Sheets("Data").Range("$F2:$AD2"), "Yes")
NAAns = Application.WorksheetFunction.CountIf(Sheets("Data").Range("$F2:$AD2"), "N/A")
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Sheets("Data").Range("AJ2:AJ" & LastRow) = (YesAns + NAAns) / 25
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JumpingCrab

Board Regular
Joined
Dec 27, 2017
Messages
96
Office Version
  1. 2019
Platform
  1. Windows
Try this:

Code:
Public Sub Calculate()
    Dim YesAns As Long
    Dim NAAns As Long
    Dim LastRow As Long
    Dim ThisRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For ThisRow = 2 To LastRow
        YesAns = Application.WorksheetFunction.CountIf(Sheets("Data").Range(Cells(ThisRow, 6), Cells(ThisRow, 30)), "Yes")
        NAAns = Application.WorksheetFunction.CountIf(Sheets("Data").Range(Cells(ThisRow, 6), Cells(ThisRow, 30)), "N/A")
        
        Sheets("Data").Cells(ThisRow, 36) = (YesAns + NAAns) / 25
    Next
End Sub

You can make the column references more dynamic of course, but if the data is always in those columns it doesn't matter.
Hope this helps.
JL
 

JumpingCrab

Board Regular
Joined
Dec 27, 2017
Messages
96
Office Version
  1. 2019
Platform
  1. Windows
Thanks, you're welcome.
Glad to be of help :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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
Top