Counting cells depending upon a cell value.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rttaksali@gmail.com - 12 Nov 2007 14:10 GMT

Hi there,

I've a table with the cell range a1:AE28.
For the odd no. rows of the table, starting with the first row the
possible values are "DG", "YL", "DR" and "DB".
For the even no. rows of the table, starting with the second row the
possible values are "G", "R" and "B".
I need a formulae to calculate the count of cells having a value as
"DG", for all the cells having values as "G".

Thanks for your help....

Rashi

p.s. here is a sample of the table, to give an idea
First row--- DB DG DG DG DG YL DB
Second row-- R R R G G G G

Reply to this Message

Don Guillett - 12 Nov 2007 14:28 GMT

??
=SUMPRODUCT((J2:P18="dg")*(J3:P19="g"))

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi there,
>
[quoted text clipped - 13 lines]
> First row--- DB DG DG DG DG YL DB
> Second row-- R R R G G G G

Reply to this Message

Ron Coderre - 12 Nov 2007 14:36 GMT

Try this:
=SUMPRODUCT((A1:AE27="DG")*(A2:AE28="G"))

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> Hi there,
>
[quoted text clipped - 13 lines]
> First row--- DB DG DG DG DG YL DB
> Second row-- R R R G G G G

Reply to this Message

rttaksali@gmail.com - 12 Nov 2007 15:02 GMT

On Nov 12, 2:36 pm, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com>
wrote:

> Try this:
> =SUMPRODUCT((A1:AE27="DG")*(A2:AE28="G"))
[quoted text clipped - 30 lines]
>
> - Show quoted text -

Thanks, but I'm afraid to say that this not what I am after. In the
sample data I posted, for cells having the 'G' the no. of counts cells
is 2 having value as 'DG'.

Reply to this Message

Ron Coderre - 12 Nov 2007 15:19 GMT

Regarding:
=SUMPRODUCT((A1:AE27="DG")*(A2:AE28="G"))

When I use that formula against your sample data the returned value is: 2.

You don't say what value it returns for you.
Did you copy it correctly?

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> On Nov 12, 2:36 pm, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com>
> wrote:
[quoted text clipped - 36 lines]
> sample data I posted, for cells having the 'G' the no. of counts cells
> is 2 having value as 'DG'.

Reply to this Message

rttaksali@gmail.com - 12 Nov 2007 15:53 GMT

On 12 Nov, 15:19, "Ron Coderre" <ronREMOVETHIScode...@bigfoot.com>
wrote:

> Regarding:
> =SUMPRODUCT((A1:AE27="DG")*(A2:AE28="G"))
[quoted text clipped - 57 lines]
>
> - Show quoted text -

Thanks, again and it is working now....

Reply to this Message

Bob Phillips - 12 Nov 2007 15:17 GMT

This gives me 2

=SUMPRODUCT(--(A1:AE1="DG"),--(A2:AE2="G"))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi there,
>
[quoted text clipped - 13 lines]
> First row--- DB DG DG DG DG YL DB
> Second row-- R R R G G G G

Reply to this Message

Rate this thread: