longbow2000
Board Regular
- Joined
- May 5, 2004
- Messages
- 59
- Office Version
- 365
- Platform
- Windows
Good Day
As part of a formula in a cell to count the unique values in a range (“A2:A10”), the following is used:
=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))
I am looking for a similar VBA worksheet function (Application.WorksheetFunction.CountIf????), but instead of referencing the rage as A2:A10, it must be cell address with variables.
As an example (myCell(x),myCol(x)): (myCell(z),myCol(z))
Based on the data layout, I can then run a loop function
Thanks in advance
As part of a formula in a cell to count the unique values in a range (“A2:A10”), the following is used:
=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))
I am looking for a similar VBA worksheet function (Application.WorksheetFunction.CountIf????), but instead of referencing the rage as A2:A10, it must be cell address with variables.
As an example (myCell(x),myCol(x)): (myCell(z),myCol(z))
Based on the data layout, I can then run a loop function
Thanks in advance
Mr_Excel.xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | LinkID_SectionID | ||||
2 | D1088_010 | # Unique Val | |||
3 | D1088_020 | 4 | |||
4 | D1088_030 | ||||
5 | D1088_030 | ||||
6 | D1088_030 | ||||
7 | D1088_030 | ||||
8 | D1088_030 | ||||
9 | D1088_030 | ||||
10 | D1088_040 | ||||
11 | D1083_010 | 2 | |||
12 | D1083_010 | ||||
13 | D1083_010 | ||||
14 | D1083_010 | ||||
15 | D1083_010 | ||||
16 | D1083_020 | ||||
WorksheetFunction |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | C3 | =SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0)) |
C11 | C11 | =SUM(IF(A11:A16<>"",1/COUNTIF(A11:A16, A11:A16), 0)) |