+ Reply to Thread
Results 1 to 6 of 6

Pls Help Me!-What formula to use at B18 to count a text

  1. #1
    Registered User
    Join Date
    01-27-2005
    Posts
    25

    Pls Help Me!-What formula to use at B18 to count a text

    Hi there!

    What formula to use at B18 to count a text or two (ex: how many red & green?) in a column, based on brand at column A (ex: brand A)?

    File attached.

    davidiew
    Attached Images Attached Images

  2. #2
    Guest

    re: Pls Help Me!-What formula to use at B18 to count a text

    Hi

    Without looking at your attachment (which I won't download) I would suggest
    you look here:
    http://www.cpearson.com/excel/colors.htm

    Hope this helps!
    Andy.

    "davidtips" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi there!
    >
    > What formula to use at B18 to count a text or two (ex: how many red &
    > green?) in a column, based on brand at column A (ex: brand A)?
    >
    > File attached.
    >
    > davidiew
    >
    >
    > +-------------------------------------------------------------------+
    > |Filename: how to count.JPG |
    > |Download: http://www.excelforum.com/attachment.php?postid=4067 |
    > +-------------------------------------------------------------------+
    >
    > --
    > davidtips
    > ------------------------------------------------------------------------
    > davidtips's Profile:
    > http://www.excelforum.com/member.php...o&userid=19103
    > View this thread: http://www.excelforum.com/showthread...hreadid=488178
    >




  3. #3
    Registered User
    Join Date
    01-27-2005
    Posts
    25

    Question Pls Help Me!

    Hi Andy

    I'm not counting the cell with colour, but text, (ex: how many "male" in B2:B50 ?) , but need to based on which Class at A2:A50, (ex: primary1, primary2 or more).

    Thanks for your helps!
    davidiew

  4. #4
    Dave Peterson
    Guest

    re: Pls Help Me!-What formula to use at B18 to count a text

    Maybe...

    =sumproduct(--(b2:b50="male"),--(a2:a50="Primary"))

    =sumproduct() likes to work with numbers. The -- stuff converts True/False's to
    1/0's.

    If you have lots of categories, you may want to look into using a pivottable.

    davidtips wrote:
    >
    > Hi Andy
    >
    > I'm not counting the cell with colour, but text, (ex: how many "male"
    > in B2:B50 ?) , but need to based on which Class at A2:A50, (ex:
    > primary1, primary2 or more).
    >
    > Thanks for your helps!
    > davidiew
    >
    > --
    > davidtips
    > ------------------------------------------------------------------------
    > davidtips's Profile: http://www.excelforum.com/member.php...o&userid=19103
    > View this thread: http://www.excelforum.com/showthread...hreadid=488178


    --

    Dave Peterson

  5. #5
    Registered User
    Join Date
    01-27-2005
    Posts
    25

    Talking Thanks again

    Thanks!

    I have try both ways, and this is the one that suited.
    May be you got others way to simplify this fomula!

    {=SUM((A1:A10="male")*(B1:B10="primary")}

    davidiew

  6. #6
    Dave Peterson
    Guest

    re: Pls Help Me!-What formula to use at B18 to count a text

    It's pretty difficult to simplify that formula. (Although, I would have added
    that final close parenthesis.)

    =SUM((A1:A10="male")*(B1:B10="primary"))
    (array entered)

    But if you share this workbook with others, you may find that array formulas can
    be broken by users who forget to hit ctrl-shift-enter after editting the
    formula.

    If I had my choice, I'd use the non-array formula:
    =SUMPRODUCT(--(A1:A10="male"),--(B1:B10="Primary"))

    I just find it a little more robust in other people's hands.

    ===
    Did you try the pivottable?

    davidtips wrote:
    >
    > Thanks!
    >
    > I have try both ways, and this is the one that suited.
    > May be you got others way to simplify this fomula!
    >
    > {=SUM((A1:A10="male")*(B1:B10="primary")}
    >
    > davidiew
    >
    > --
    > davidtips
    > ------------------------------------------------------------------------
    > davidtips's Profile: http://www.excelforum.com/member.php...o&userid=19103
    > View this thread: http://www.excelforum.com/showthread...hreadid=488178


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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