Please help, COUNTA AND COUNTIF SAME FORMULA

[Closed]
Report
Posts
2
Registration date
Thursday March 26, 2015
Status
Member
Last seen
March 27, 2015
-
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
-
Hello,

Please help me, I am trying to accomplish is this:

I would like to count the text in Range (B1:b5) excluding duplicates, however only if the text in range(a1:a5) matches exactly for example listed below lets say Stephanie.

LSP Customer policy number
stephanie tom 1232
jessica jerry 8768
stephanie jack 87576
stephanie jack 9876987
jessica hank 9876769

So the correct formula should come back with the number 2. Driving myself crazy over this ugh. In appreciate any assistance I can get. Thank you so much for your time and consideration.

1 reply

Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
Hi stephmbingham,

Not sure if this can be done by formula's alone.

The following will happen when you run the code below:
- Copy the used range in columns A & B and paste it temporary in columns G & H.
- Remove the duplicates in columns A & B.
- The code will ask you to input the name to search for.
- Run the COUNTIF formula and put the result in E2.
- Restore the duplicates in columns A & B by cutting the data in columns G & H back to columns A & B.

Check the column references in the code and possibly adjust column G & H to any spare columns you might have.
Also check E2 if it is a suitable location for displaying the result.

Here is the code:
Sub RunMe()
Dim lRow As Long

lRow = Range("B" & Rows.Count).End(xlUp).Row

Range("A2:B" & lRow).Copy Range("G2")
Range("A2:B" & lRow).RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
Range("E2").Value = Application.WorksheetFunction.CountIf(Range("A2:B" & lRow), InputBox("Enter the name to count:"))
Range("G2:H" & lRow).Cut Range("A2")
End Sub


How to implement and run a code:

- From Excel hit Alt + F11 to open the "Microsoft Visual Basic" window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro's.
- Double-click the macro you wish to run.
NOTE: macro's cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!