Counting values in a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khalil Handal - 06 Nov 2006 18:26 GMT

Hi to all,

Range C5:C32 contains letters either "C" or "M"

Range D5:D32 contains letters either "M" or "F"

I want to count the number of combinations of the letters which are as
follows:

CM

CF

MM

MF

Starting cell D35 growing down to cell D38

I tried to write an array formula but always having an error.

Thanks in Advance

Khalil Handal

Reply to this Message

jeff.t@virgin.net - 07 Nov 2006 22:27 GMT

Hi

I'd simply use columns E, F, G, & H (which can be hidden if wanted) and
insert the following in each:-

E =IF(AND(C5="C",D5="M"),1,0)

F =IF(AND(C5="C",D5="F"),1,0)

G =IF(AND(C5="M",D5="M"),1,0)

H =IF(AND(C5="M",D5="F"),1,0)

Then sum each of these columns in row 33 and use in cell D35 " =E33",
D36 " =F33" etc

It's not elegant but simple and it works

Jeff

> Hi to all,
>
[quoted text clipped - 20 lines]
>
> Khalil Handal

Reply to this Message

Dave Peterson - 08 Nov 2006 00:01 GMT

To count CM's:

=sumproduct(--(c5:c32="C"),--(d5:d32="M"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

> Hi to all,
>
[quoted text clipped - 20 lines]
>
> Khalil Handal

Signature

Dave Peterson

Reply to this Message

Adel Handal - 08 Nov 2006 06:34 GMT

Hi,
thanks a lot!

Khalil

> To count CM's:
>
[quoted text clipped - 36 lines]
>>
>> Khalil Handal

Reply to this Message

Rate this thread: