Please help, COUNTA AND COUNTIF SAME FORMULA
Closed
stephmbingham
Posts
1
Registration date
Thursday 26 March 2015
Status
Member
Last seen
27 March 2015
-
27 Mar 2015 à 10:23
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 31 Mar 2015 à 11:59
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 31 Mar 2015 à 11:59
Related:
- Please help, COUNTA AND COUNTIF SAME FORMULA
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Spreadsheet formula - Guide
- Grade formula in excel marksheet - Guide
- Credit summation formula - Guide
- Formula to calculate vat in excel - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
31 Mar 2015 à 11:59
31 Mar 2015 à 11:59
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
