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?

[TABLE="width: 781"]
<colgroup><col span="6"><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]99[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]97[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]96[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]95[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]94[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]93[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]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 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=SUM( -- (FREQUENCY(IF(Calls1!B:B = "1",Calls1!A:A),Calls1!A:A)>0)) [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]If anyone can help please.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]this formula labels compliant rows[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]in D1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]=IF(AND(COUNTIF($A$1:$A$20,A1)=1,B1=1),"yes","")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.