Hi to all,

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

Range D532 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

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


Khalil Handal wrote:
> Hi to all,
>
> Range C5:C32 contains letters either "C" or "M"
>
> Range D532 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

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



Khalil Handal wrote:
>
> Hi to all,
>
> Range C5:C32 contains letters either "C" or "M"
>
> Range D532 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

--

Dave Peterson

Hi,
thanks a lot!

Khalil

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> Khalil Handal wrote:
>>
>> Hi to all,
>>
>> Range C5:C32 contains letters either "C" or "M"
>>
>> Range D532 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
>
> --
>
> Dave Peterson