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
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 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
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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