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

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

Hope this helps!
Andy.

"davidtips" wrote
in message ...

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


--
davidtips
------------------------------------------------------------------------
davidtips's Profile: http://www.excelforum.com/member.php...o&userid=19103
View this thread: http://www.excelforum.com/showthread...hreadid=488178

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


--
davidtips
------------------------------------------------------------------------
davidtips's Profile: http://www.excelforum.com/member.php...o&userid=19103
View this thread: http://www.excelforum.com/showthread...hreadid=488178

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