Count number of group members

[Lana74]

Hello and Happy New Year to all!

I dont have access to Excel on this machine so I'll try to explain without it. Please bear with me

On Sheet1, I have 2columns: a list of unique items and group they belong to:

Item: Group
Item1 1
Item2 1
Item3 1
Item10 2
Item25 2
Item155 3

Again, all item names are unique.

On Sheet2 in column A, I have a random list of Items from column A above.

What I need to do is to figure out how many items from each group are on that list and get that number in column B of sheet2

I know how to do it in 2 steps, but I'm trying to figure it out how to do it in one step.

Here is how I do it now:

In column B of sheet2, I figure out what group the item belongs to: =sumif(sheet1!CoulmnA:A,A1,sheet1!CoulmnB:B)
In column C of sheet2, I count number of appearances in column B: =countIf(ColumnB:B,B1)

So I guess my quiestion is: how do I combine two formulas above into one.

Thank you so much in advance for you help! Please let me know if any of this is unclear.

[benishiryo]

see. your C1 formula is:
=countIf(ColumnB:B,B1)

you are using B1. so just combine what you got in B1:
=COUNTIF(Sheet1!B:B,SUMIF(Sheet1!A:A,A1,Sheet1!B:B))

[Lana74]

Thank you for replying, benishiryo!

Unfortunately, that's not exactly what I need. I'm attaching the actual file this time. Sorry for confusion.

Column A is a list of uniquely named factors
Column B is a group each factor belongs to (I color coded each group)

Column F contains random list of factors, all exist in column A.
I need to identify which factors in column F is a lonely representative of its group (column B)
I achieve that now in 2 steps as you will see in columns G and H.
I highlighted red 2 rows that I need to identify.

So my question is: how do I achieve what I'm doing now in 2 steps (Column G and H) in one step.

Please let me know if this is clearer now.

Thanks!!!

[benishiryo]

maybe:
=SUMPRODUCT(--(SUMIF(A:A,$F$2:$F$15,B:B)=SUMIF(A:A,F2,B:B)))

you can even go further with conditional formatting to highlight those that are lone representatives and omit using the formula. select the range you want to apply to (say from F2:F15)
go to Home tab -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=SUMPRODUCT(--(SUMIF(A:A,$F$2:$F$15,B:B)=SUMIF(A:A,F2,B:B)))=1
format color

[Lana74]

Brilliant! Just brilliant! Thank you so much!