If and CountIF combo?

[mcarp]

Col1 Col2
1____a
1____a
1____b
2____c
3____d
3____d

I need a formula in column 3 that can count how many different letters there are in column 2 based for each value in column 1

So in this instance since the "1" in col1 has both an a and a b associated with it, i need it to give me a count of 2. (In the case of 3, it only is associated with d, so a count of 1 is appropriate)

Col1 Col2 Col3
1____a___2
1____a___2
1____b___2
2____c___1
3____d___1
3____d___1

Please let me know if this is not clear

[Sam Capricci]

there are quite a few ways to do it. One would be to use a concatenate. so if the columns are as you note, you could do this in col3 =(A1&B1) then in col4 =COUNTIF(C:C,A1&B1)

EDIT: of course best then would be to create a distinct table (w/o duplicates) elsewhere then base the count on that. Then you get a distinct count based on specific single instances.

[sktneer]

Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter.

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

[mcarp]

Yes that was exactly what I was looking for sktneer! thanks!

[sktneer]

You're welcome. Thanks for the feedback.