Count functions

[Jalal]

Hi,

I would like to count the number of records which have a certain value in
one column and a certain value in a different column.

E.g. Each record in column B is marked A-C
Each record in column C is marked Y or N

I would like to count how many records are marked A and Y; A and N; B and Y;
B and N; C and Y; C and N

Is this possible?

 

[Domenic]

Have a look here...

http://www.xl-central.com/count-multiple-criteria.html

Hope this helps!

http://www.xl-central.com

 

[Jalal]

Hi Domenic,

Many thanks for your speedy response.

I have used the SUMPRODUCT function you suggested...
=SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2))

and it works!...

Now... what about if I have 3 variables... is it possible to count the
number of records that conform to the following:

A-Y-JJ
B-Y-JJ
C-Y-JJ
A-N-JJ
B-N-JJ
C-N-JJ
A-Y-AC
B-Y-AC
C-Y-AC
A-N-AC
B-N-AC
C-N-AC
A-Y-NB
B-Y-NB
C-Y-NB
A-N-NB
B-N-NB
C-N-NB

etc. etc.?

I have tried to add it to the SUMPRODUCT formula you initially suggested but
get an impossibly HIGH answer - it cannot be correct

e.g. this is the formula i created
=SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2)--(C2:$C$65536=V2))

Any suggestions?

 

[Domenic]

I have tried to add it to the SUMPRODUCT formula you initially suggested but
get an impossibly HIGH answer - it cannot be correct

e.g. this is the formula i created
=SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2)--(C2:$C$65536=V2))

Any suggestions?

There's a comma missing between the second and third argument. Try...

=SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2),--(C2:$C$65536=V2))

Also, if your data does not actually extent all the way to Row 65536 and
you're using Excel 2003, convert your data into a list...

Data > List > Create List

The ranges will automatically adjust as data is added/removed. If
you're using an earlier version, you can use dynamic named ranges.

 

[Jalal]

Perfect

Both tips work a treat - many thanks!

There's a comma missing between the second and third argument. Try...

=SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2),--(C2:$C$65536=V2))

Also, if your data does not actually extent all the way to Row 65536 and
you're using Excel 2003, convert your data into a list...

Data > List > Create List

The ranges will automatically adjust as data is added/removed. If
you're using an earlier version, you can use dynamic named ranges.