Please help, COUNTA AND COUNTIF SAME FORMULA
Closed
stephmbingham
Posts
1
Registration date
Thursday March 26, 2015
Status
Member
Last seen
March 27, 2015
-
Mar 27, 2015 at 10:23 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 31, 2015 at 11:59 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 31, 2015 at 11:59 AM
Related:
- Please help, COUNTA AND COUNTIF SAME FORMULA
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Credit summation formula - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Mar 31, 2015 at 11:59 AM
Mar 31, 2015 at 11:59 AM
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:
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
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
