+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    10-13-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    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. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-13-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1