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
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
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
>
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
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
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks