Count unique numbers in 1 column that have specific data in a second column?

[whudson1980]

[FONT=&quot]I need an excel forumula that counts unique ( no duplicates and no blanks ) in Column A and that have a "1" in Column B. I found the below forumula online, but its returning a "#VALUE" when it should be returning a number [/FONT]

[FONT=&quot]=SUM( -- (FREQUENCY(IF(Calls1!B:B = "1",Calls1!A:A),Calls1!A:A)>0)) [/FONT]

[FONT=&quot]If anyone can help please.[/FONT]

 

[oldbrewer]

Re: Excel Forumula to count unique numbers in 1 column that have specific data in a second column?

21	1
6	2
	3
9	1		yes
	2
21	3
44	1		yes
43	2
43	3
42	1		yes
41	2
40	3
99	1		yes
	2
98	3
97	1		yes
96	2
95	3
94	2
93	1		yes
						I need an excel forumula that counts unique ( no duplicates and no blanks ) in Column A and that have a "1" in Column B. I found the below forumula online, but its returning a "#VALUE" when it should be returning a number
						=SUM( -- (FREQUENCY(IF(Calls1!B:B = "1",Calls1!A:A),Calls1!A:A)>0))
						If anyone can help please.
						this formula labels compliant rows
						in D1
						=IF(AND(COUNTIF($A$1:$A$20,A1)=1,B1=1),"yes","")

<colgroup><col span="6"><col><col span="2"></colgroup><tbody>
</tbody>

 

[whudson1980]

Re: Excel Forumula to count unique numbers in 1 column that have specific data in a second column?

This doesn't quite get the result that I need, and I'm not sure what you mean by "compliant rows" being that there are multiple compliant rows here that aren't showing compliant.

In your example, I would need an output that shows how many unique numbers in Column A that have a "1" in column B, so the output shouldn't be yes or blank, but instead should be: 7

 

[whudson1980]

Re: Excel Forumula to count unique numbers in 1 column that have specific data in a second column?

I now see what you were trying to do. . . then I could count the number of YES, but the first row should have returned a YES also, and thats the problem I'm having here. The 21 is unique, and Column B has a 1, so there should have been a YES there.

 

[whudson1980]

Re: Excel Forumula to count unique numbers in 1 column that have specific data in a second column?

So you know, this below forumula gives me the correct number for column A ( removing duplicates ) but I just need to add to it somehow to give me a count of how many of THOSE have a 1 in column B

=SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))

 

[oldbrewer]

Re: Excel Forumula to count unique numbers in 1 column that have specific data in a second column?

there is another 21 so 21 is not unique

excel has hundreds of unused columns - I use some of them to make life easier - as you say a count of the "yesses" is all you need....

 

[Marcelo Branco]

Re: Excel Forumula to count unique numbers in 1 column that have specific data in a second column?

So you know, this below forumula gives me the correct number for column A ( removing duplicates ) but I just need to add to it somehow to give me a count of how many of THOSE have a 1 in column B

=SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))

Maybe this array formula
=SUM(IF(FREQUENCY(IF(B1:B10=1,A1:A10),A1:A10)>0,1))
Confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[whudson1980]

Re: Excel Forumula to count unique numbers in 1 column that have specific data in a second column?

there is another 21 so 21 is not unique

excel has hundreds of unused columns - I use some of them to make life easier - as you say a count of the "yesses" is all you need....

Maybe I wasn't clear enough, I apologize. I was looking to eliminate duplicates. Thats what I meant by "unique", so the first 21 should have counted, and the second one ignored.

Maybe this array formula
=SUM(IF(FREQUENCY(IF(B1:B10=1,A1:A10),A1:A10)>0,1))
Confirmed with Ctrl+Shift+Enter, not just Enter

M.

You sir, are a genious. . . . I can't believe my problem was merely hitting enter instead of ctrl shift and enter. Who at MS thought that was a good idea? lol. . . Anyway, thank you sir. You saved the day, and with that bit of information, my original formula worked like a charm.