CountIf & Unique Values

[tangerine7199]

Good Morning

I need to do a count of people in a school (A) based on their value in columnB. Duplicate people are OK. Duplicate people in a school is not OK. I would want only the count of Unique ID-Schools if their value in B is 1

Example

A B
[TABLE="width: 146"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]ID-School[/TD]
[TD]First Year[/TD]
[/TR]
[TR]
[TD]123-school1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]555-school2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]999-school3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]123-school1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]999-school3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]555-school2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



any help will be appreciated

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF($B$2:$B$7=D2,IF(LEN($A$2:$A$7)>0,MATCH("~"&$A$2:$A$7,$A$2:$A$7&"",0))),ROW($A$2:$A$7)-ROW($A$2)+1)>0,1))

...where D2 contains the value of interest, such as 1.

Hope this helps!

 

[tangerine7199]

Thank you so much! but i think i need to clarify:

ID-School is column A
First Year is column B (and that's where the one is)

does this change the formula?

 

[Domenic]

No need to change the formula, other than to adjust the ranges to include all your data.

 

[tangerine7199]

it worked! THANK YOU SO MUCH

And if I wanted a count by school, while you're here?

 

[Domenic]

You're very welcome. Glad I could help.

I'm not sure what you mean. Can you please elaborate, and provide the actual results you expect based on your example?

 

[tangerine7199]

Your formula gave me a total of unique ID AND School. To go a little further, how could i get a count of unique id's by school? Does this image help?

 

[tangerine7199]

https://drive.google.com/open?id=0B0xPpXcjgToySlM3OFdSTjZqdkE

 

[Domenic]

Based on the data you provide in your image, what are the actual results you expect for School1, School2, and School3?