Counting values in a range

[Khalil Handal]

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

 

[jeff.t]

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

 

[Dave Peterson]

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

 

[Adel Handal]

Hi,
thanks a lot!

Khalil

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