Count functions

J

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?
 
J

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?
 
D

Domenic

Jalal said:
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.
 
J

Jalal

Perfect

Both tips work a treat - many thanks!

Domenic said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top