Total unique with other criteria help!

[Rob]

Hi, I'm new to Excel so please bear with me!
I'm trying to create a summary sheet. Here's what I'm
trying to do:
Total of unique numbers from column B14:B200
This works
=SUMPRODUCT((B14:B200<>"")/COUNTIF(B14:B200,B14:B200&""))

Could someone please show me how to meet additional
criteria from another column?

Each number in column B could have a corresponding entry
of either:
SS, TT, PP or MM in column C14:C200.

Not every number in column B has an entry in C.

If the number in column B is on the list more than once,
the entry in column C (if any) is the same in all
instances.

So, if the total unique in B= 29,
I'll end up with a further breakdown like
SS=9
TT=6
PP=7
MM=4
no entry=3

If someone could show me how to get the SS total and the
no entry total, I can take it from there!

I hope I haven't overexplained myself!
Thanks in advance.
Rob

 

[Harlan Grove]

Each number in column B could have a corresponding entry
of either: SS, TT, PP or MM in column C14:C200.

Not every number in column B has an entry in C.

So far, so good.

If the number in column B is on the list more than once,
the entry in column C (if any) is the same in all
instances.

Meaning if B5 and B99 were both 123, then C5 and C99 would be the same?

So, if the total unique in B= 29,
I'll end up with a further breakdown like
SS=9
TT=6
PP=7
MM=4
no entry=3

....

For SS, use the array formula

=COUNT(1/FREQUENCY(IF(C14:C200="SS",B14:B200),B14:B200))

For no entry, replace "SS" with "".

 

[Rob]

Hi, thanks for the quick response!
I've entered your array, but for some reason it doesn't
work for me....(yes...entered "Ctrl/Shift/Enter)

=COUNT(1/FREQUENCY(IF(C14:C200="SS",B14:B200),B14:B200))

I'll have to play around with this to see what's going
on...it's not calculating properly. If I enter "SS" in
column C, for example, it'll work in some cells, but not
all. It seems to work randomly, I can't see a pattern.
(all cells are formatted the same).

 

[hgrove]

Rob wrote...

I've entered your array, but for some reason it doesn't work for
me....(yes...entered "Ctrl/Shift/Enter)

=COUNT(1/FREQUENCY(IF(C14:C200="SS",B14:B200),B14:B200))

...

You have to be more precise about how it's not working. Does it alway
return zero? Does it return error values? If so, which? Does it retr
numbers just not the correct numbers?

If you're entering this in other cells, are you entering it exactly o
are you copying and pasting without making the references absolute?

I tested the formula before I posted it originally, and I've tested i
again. It works if there are *exact* matches in C14:C200, but it'l
return zero if the entries in C14:C200 have trailing spaces

 

[Guest]

Hi again...and thanks, I'm sure your formula will work,
my copy of excel may be corrupt, so I'll do a repair and
see what happens.
Thanks again