Counting and Displaying Totals Automatically Using Excel VBA

How to count and display totals automatically using Excel VBA. Here are two queries:

Hi,
I’m working as a Quality Assurance Engineer.
I have an excel sheet that has testing details.
Column A has the Requirement Id, B has the test case no under that requirement, and C has the test case Pass/Fail status, as shown below:
Column A                     Column B                    Column C
ID                      Test Case no.                 Status
FID 1                            TC-001                           Pass
FID 1                            TC-002                           Fail
FID 2                            TC-003                           Pass
FID 2                            TC-004                           Pass
Now when I click on a “Submit” button, the total pass/fail count for each requirement should be updated to another sheet. As below.
ID                  No of Pass              No of Fail
FID 1                             1                             1
FID 2                             2                             0
Is this possible Sir?
Regards,
Atul


Hello Sir, my question is How to find duplicate value of same ID and sum all the values of same ID without any of traditional excel formulas like count if, match and so on.

We can solve the above questions using Visual Basic for Applications – VBA – quickly and easily. Watch the training video before you study the VBA code:


Watch the video on YouTube.

Sub countPassFail()

Dim lastrow As Long, countpass1 As Long, countfail1 As Long
Dim erow As Long, countpass2 As Long, countfail2 As Long

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

countpass1 = 0
countfail1 = 0
countpass2 = 0
countfail2 = 0

For i = 2 To lastrow
If Sheet1.Cells(i, 1) = “FID 1” And Sheet1.Cells(i, 3) = “Pass” Then
countpass1 = countpass1 + 1
ElseIf Sheet1.Cells(i, 1) = “FID 1” And Sheet1.Cells(i, 3) = “Fail” Then
countfail1 = countfail1 + 1
ElseIf Sheet1.Cells(i, 1) = “FID 2” And Sheet1.Cells(i, 3) = “Pass” Then
countpass2 = countpass2 + 1
ElseIf Sheet1.Cells(i, 1) = “FID 2” And Sheet1.Cells(i, 3) = “Fail” Then
countfail2 = countfail2 + 1
End If
Next i

MsgBox “Pass Count of FID 1,” & ” ” & countpass1 & ” ” & “Fail Count of FID 1,” & ” ” & countfail1 & vbCrLf & “Pass Count of FID 2,” & ” ” & countpass2 & ” ” & “Fail Count of FID 2,” & ” ” & countfail2
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
‘Sheet2.Select
Sheet2.Range(“A2:C500”).Clear
Sheet2.Cells(erow, 1) = “FID 1”
Sheet2.Cells(erow, 2) = countpass1
Sheet2.Cells(erow, 3) = countfail1
erow = erow + 1
Sheet2.Cells(erow, 1) = “FID 2”
Sheet2.Cells(erow, 2) = countpass2
Sheet2.Cells(erow, 3) = countfail2

End Sub

Of course if we don’t wish to use VBA, we can use the database function DCOUNTA to solve a problem like this:

DCOUNTA Function to count total pass status of item FID 1

DCOUNTA Function to count total pass status of item FID 1


In the above image we have shown how to count the total of ‘Pass’ for item FID 1. You can find the total of fail also in a similar fashion also for item FID 2.

Download a sample file:


Leave a Reply

Your email address will not be published. Required fields are marked *