I've got 2 columns of data, let's say that they are as follows :-



20 3ES
21 3EM
30 8AS
20 8AS
30 3ES
20 3ES
50 9A
60 9B

I want to count the number of entries wher the values meet two
criteria. The criteria are defined by two cells.

Let's Say I key in 20 in the first look up cell and 3ES in the second
look up cell. The correct answer to the above is 2. That is the first
and the sixth lines meet both criteria.

Help please


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile: http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=542357

Hi!

Try this:

C1 = 20
D1 = 3ES

=SUMPRODUCT(--(A1:A8=C1),--(B1:B8=D1))

Biff

"JohnHill" wrote in
message ...

I've got 2 columns of data, let's say that they are as follows :-



20 3ES
21 3EM
30 8AS
20 8AS
30 3ES
20 3ES
50 9A
60 9B

I want to count the number of entries wher the values meet two
criteria. The criteria are defined by two cells.

Let's Say I key in 20 in the first look up cell and 3ES in the second
look up cell. The correct answer to the above is 2. That is the first
and the sixth lines meet both criteria.

Help please


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile:
http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=542357

Hi John
You could do it in 2 ways.
1. The simpler and easier one is to add a "helper column" C, while the
numbers - 20, 30 etc are in Column A1 to A8, and 3ES etc are in B1 to B8. In
C1, write a formula "=AND(A1=$F$1,B1=$F$2)" where cell F1 contains 20 and F2
contains 3ES. Drag the formula down.
In the cell where you want the result, write "=COUNTIF(C1:C8,TRUE)"
You will get the results.
2. In the results cell write "=SUM((A1:A8=F1)*(B1:B8=F2))". This has to be
an array formula, so please press the keys "SHIFT-CNTRL-ENTER instead of just
enter after typing the formula.
Regards
Sukhjeet

"JohnHill" wrote:


I've got 2 columns of data, let's say that they are as follows :-



20 3ES
21 3EM
30 8AS
20 8AS
30 3ES
20 3ES
50 9A
60 9B

I want to count the number of entries wher the values meet two
criteria. The criteria are defined by two cells.

Let's Say I key in 20 in the first look up cell and 3ES in the second
look up cell. The correct answer to the above is 2. That is the first
and the sixth lines meet both criteria.

Help please


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile: http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=542357

Hi there,
I would make a third column C of values by concatenating the other two
columns
eg in cell C1 write =A1&B1. This will give you 20 and 3ES merged
together in one cell ie c1 will equal 20 3ES. You can then have your
input cells say e5 e6 where you can enter your criteria. Concatenate
these onto another cell eg e7. Then, use a countif formula to get your
result eg =countif(c1:c8,e7). That should do it
You can change C1:C8 to C:C if you want to look at the whole column but
think about reducing calculation time by just looking at the cells
that need looking at.

I hope this helps

Cheers

Simon


--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235
View this thread: http://www.excelforum.com/showthread...hreadid=542357

Biff's solution is more elegant!

"Sukhjeet" wrote:

Hi John
You could do it in 2 ways.
1. The simpler and easier one is to add a "helper column" C, while the
numbers - 20, 30 etc are in Column A1 to A8, and 3ES etc are in B1 to B8. In
C1, write a formula "=AND(A1=$F$1,B1=$F$2)" where cell F1 contains 20 and F2
contains 3ES. Drag the formula down.
In the cell where you want the result, write "=COUNTIF(C1:C8,TRUE)"
You will get the results.
2. In the results cell write "=SUM((A1:A8=F1)*(B1:B8=F2))". This has to be
an array formula, so please press the keys "SHIFT-CNTRL-ENTER instead of just
enter after typing the formula.
Regards
Sukhjeet

"JohnHill" wrote:


I've got 2 columns of data, let's say that they are as follows :-



20 3ES
21 3EM
30 8AS
20 8AS
30 3ES
20 3ES
50 9A
60 9B

I want to count the number of entries wher the values meet two
criteria. The criteria are defined by two cells.

Let's Say I key in 20 in the first look up cell and 3ES in the second
look up cell. The correct answer to the above is 2. That is the first
and the sixth lines meet both criteria.

Help please


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile: http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=542357

Gidday Biff ....

Thanks for that ... can you tell me what does the -- do ??

Ripper Boris !!!


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile: http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=542357

Try this little experiment:

Enter 20 in cell A1

Enter these formulas in cells B1 and C1:

B1: =(A1=20)
C1 =--(A1=20)

See what happens? Now clear the contents of cell A1.

This is what's happening inside the Sumproduct formula except that instead
of it happening to a single cell as in the experiement, it's happening to 2
individual arrays of cells.

This happens to both arrays:

--(A1:A8=C1)
--(B1:B8=D1)

Then you end up with something like this:

1.......0
0.......0
1.......1
0.......1

These 2 arrays are then multiplied together:

1 * 0 = 0
0 * 0 = 0
1 * 1 = 1
0 * 1 = 0

The result of the multiplication is then summed:

=SUMPRODUCT({0;0;1;0}) = 1

For more info see:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

Biff

"JohnHill" wrote in
message ...

Gidday Biff ....

Thanks for that ... can you tell me what does the -- do ??

Ripper Boris !!!


--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile:
http://www.excelforum.com/member.php...o&userid=25171
View this thread: http://www.excelforum.com/showthread...hreadid=542357