I need a formula like a SUMIF, but for counting different text?

1. I need a formula like a SUMIF, but for counting different text?

So I have table like this:

FIFI BLACK
FIFI RED
FIFI BLACK
FIFI CREAM
FIFI RED
FIFI RED
LOVE WHITE
LOVE PEACH
LOVE PEACH

In a seperate table on another sheet, I want to count how many different colours there are relating to the word in the left cell. So, as 'Fifi' comes in 3 colours (black, red and cream) and Love comes in 2 colours (white and peach), the result I would want to see is this:

FIFI 3
LOVE 2

I have been trying to do this all day! Can anyone help me out?

(I hope I've explained that well enough).

2. Re: I need a formula like a SUMIF, but for counting different text?

Try a formula like this

=SUM(IF(FREQUENCY(IF(A\$2:A\$20=D2,IF(B\$2:B\$20<>"",MATCH(B\$2:B\$20,B\$2:B\$20,0))),ROW(B\$2:B\$20)-ROW(B\$2)),1))

confirmed with CTRL+SHIFT+ENTER

where D2 contains a specific word like FIFI

3. Re: I need a formula like a SUMIF, but for counting different text?

Brilliant! Worked. I knew the formula was there somewhere. Thank you.

There are currently 1 users browsing this thread. (0 members and 1 guests)