SUM and COUNT

[Ellasheba]

Hello
I would like to be able to understand how to identify a count of unique numbers in one column and then look to a second column and count with an entry in to return a count.
I have been using some functions but cannot work out how to combine the two to provide the answer I am looking for. It is almost a grouping type function???
thank you

Book1a.xlsx
	A	B	C	D	E
1	1
2	1
3	1
4	1
5	1	05/01/2012
6	1
7	1	01/01/2012
8	2
9	2
10	2	02/02/2013
11	2
12	2
13	3
14	3
15	3
16	3
17	3
18	3
19	3	3		Answer I am looking for	2
Sheet2

Cell Formulas
Range	Formula
A19	A19	=SUM(1/COUNTIF(A1:A18,A1:A18))
B19	B19	=COUNTA(B1:B18)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KRice]

I don't quite understand what the objective is. I believe you want a formula to count the number of unique entries in Column A. Then what do you want that involves column B?

 

[Ellasheba]

I don't quite understand what the objective is. I believe you want a formula to count the number of unique entries in Column A. Then what do you want that involves column B?

Column B is used to see how many entries relate to each unique number from column A.

A = 1 count of Colum B will be 1
A = 2 count of Column B will be 1
A =3 Count of column B is zero as there are no entries relating to A in B

 

[KRice]

I still don't follow. For the rows where column A = 1, there are two entries in column B that have values (rows 5 and 7). So wouldn't the correct answer be 2 for A=1...and 1 for A=2...and 0 for A=3?

 

[Ellasheba]

I still don't follow. For the rows where column A = 1, there are two entries in column B that have values (rows 5 and 7). So wouldn't the correct answer be 2 for A=1...and 1 for A=2...and 0 for A=3?

Yes there are two entries but I don’t need to clunt the unique numbers in column B just the fact that there is at least one cell in column B filled in relating to the unique reference in Column A ... I am maybe making it more confusing than it needs to be .... ?

 

[Ellasheba]

Yes there are two entries but I don’t need to clunt the unique numbers in column B just the fact that there is at least one cell in column B filled in relating to the unique reference in Column A ... I am maybe making it more confusing than it needs to be .... ?

Or I am using the wrong terminology ...

 

[KRice]

I think I understand now...thanks for the clarification. I believe you want to evaluate the question: Of the unique values in column A, how many of them have at least one entry in column B?

 

[Ellasheba]

I think I understand now...thanks for the clarification. I believe you want to evaluate the question: Of the unique values in column A, how many of them have at least one entry in column B?

Yes that’s it... I’m sorry I’m not explaining myself very well

 

[KRice]

Which version of Excel are you using? You can edit your account details to show this. You may have access to more advanced functions that simplify this.

 

[Ellasheba]

Which version of Excel are you using? You can edit your account details to show this. You may have access to more advanced functions that simplify this.

I am using Excel 2010 and 2013 ....