Formula for Counts

[menor59]

Hello all,

formula needed for the following please if you can help

A1 B1 D1 E1
Dog fido Dog 4
dog jess Cat 3
dog rover
dog max
cat purr
cat kitty
cat jax



A1 is a list of Duplicates; lets call that a catagory
B1 are Names assigned to that catagory
D1 is A1 without Duplicates
E1 is needed to compare D1 to range A1 (Range) and count the number of names for dogs and cats..From B1

Please help

 

[Legacy 143009]

=SUM(--(C1=A1:A7))

It's an array formula so you have to Ctrl+Shift+Enter after paste.

 

[Ron Coderre]

with your sample data in A1:D7
This regular formula, copied down, returns the count of unique names for the referenced category:

E1: =SUMPRODUCT(ISNUMBER(SEARCH(D1,$A$1:$A$19))/COUNTIF($B$1:$B$19,$B$1:$B$19&""))

(Duplicate combinations are only counted once)

Is that something you can work with?

 

[Joyner]

Hello, are you looking for a COUNTIFS:

=COUNTIFS($A$1:$A$7,C1)

If you at looking for dog / cat unique names, can you use a Pivot Table?

 

[Aladin Akyurek]

In E1 enter and copy down:

=COUNTIFS($A$1:$A$7,C1,$B$1:$B$7,"?*")