software

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: www.excelforum.com/member.php...oamp;userid=25171
View this thread: www.excelforum.com/showthread...hreadid=542357Hi!

Try this:

C1 = 20
D1 = 3ES

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

Biff

quot;JohnHillquot; gt; wrote in
message ...
gt;
gt; I've got 2 columns of data, let's say that they are as follows :-
gt;
gt;
gt;
gt; 20 3ES
gt; 21 3EM
gt; 30 8AS
gt; 20 8AS
gt; 30 3ES
gt; 20 3ES
gt; 50 9A
gt; 60 9B
gt;
gt; I want to count the number of entries wher the values meet two
gt; criteria. The criteria are defined by two cells.
gt;
gt; Let's Say I key in 20 in the first look up cell and 3ES in the second
gt; look up cell. The correct answer to the above is 2. That is the first
gt; and the sixth lines meet both criteria.
gt;
gt; Help please
gt;
gt;
gt; --
gt; JohnHill
gt; ------------------------------------------------------------------------
gt; JohnHill's Profile:
gt; www.excelforum.com/member.php...oamp;userid=25171
gt; View this thread: www.excelforum.com/showthread...hreadid=542357
gt;
Hi John
You could do it in 2 ways.
1. The simpler and easier one is to add a quot;helper columnquot; 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 quot;=AND(A1=$F$1,B1=$F$2)quot; where cell F1 contains 20 and F2
contains 3ES. Drag the formula down.
In the cell where you want the result, write quot;=COUNTIF(C1:C8,TRUE)quot;
You will get the results.
2. In the results cell write quot;=SUM((A1:A8=F1)*(B1:B8=F2))quot;. This has to be
an array formula, so please press the keys quot;SHIFT-CNTRL-ENTER instead of just
enter after typing the formula.
Regards
Sukhjeet

quot;JohnHillquot; wrote:

gt;
gt; I've got 2 columns of data, let's say that they are as follows :-
gt;
gt;
gt;
gt; 20 3ES
gt; 21 3EM
gt; 30 8AS
gt; 20 8AS
gt; 30 3ES
gt; 20 3ES
gt; 50 9A
gt; 60 9B
gt;
gt; I want to count the number of entries wher the values meet two
gt; criteria. The criteria are defined by two cells.
gt;
gt; Let's Say I key in 20 in the first look up cell and 3ES in the second
gt; look up cell. The correct answer to the above is 2. That is the first
gt; and the sixth lines meet both criteria.
gt;
gt; Help please
gt;
gt;
gt; --
gt; JohnHill
gt; ------------------------------------------------------------------------
gt; JohnHill's Profile: www.excelforum.com/member.php...oamp;userid=25171
gt; View this thread: www.excelforum.com/showthread...hreadid=542357
gt;
gt;


Hi there,
I would make a third column C of values by concatenating the other two
columns
eg in cell C1 write =A1amp;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: www.excelforum.com/member.php...oamp;userid=34235
View this thread: www.excelforum.com/showthread...hreadid=542357Biff's solution is more elegant!

quot;Sukhjeetquot; wrote:

gt; Hi John
gt; You could do it in 2 ways.
gt; 1. The simpler and easier one is to add a quot;helper columnquot; C, while the
gt; numbers - 20, 30 etc are in Column A1 to A8, and 3ES etc are in B1 to B8. In
gt; C1, write a formula quot;=AND(A1=$F$1,B1=$F$2)quot; where cell F1 contains 20 and F2
gt; contains 3ES. Drag the formula down.
gt; In the cell where you want the result, write quot;=COUNTIF(C1:C8,TRUE)quot;
gt; You will get the results.
gt; 2. In the results cell write quot;=SUM((A1:A8=F1)*(B1:B8=F2))quot;. This has to be
gt; an array formula, so please press the keys quot;SHIFT-CNTRL-ENTER instead of just
gt; enter after typing the formula.
gt; Regards
gt; Sukhjeet
gt;
gt; quot;JohnHillquot; wrote:
gt;
gt; gt;
gt; gt; I've got 2 columns of data, let's say that they are as follows :-
gt; gt;
gt; gt;
gt; gt;
gt; gt; 20 3ES
gt; gt; 21 3EM
gt; gt; 30 8AS
gt; gt; 20 8AS
gt; gt; 30 3ES
gt; gt; 20 3ES
gt; gt; 50 9A
gt; gt; 60 9B
gt; gt;
gt; gt; I want to count the number of entries wher the values meet two
gt; gt; criteria. The criteria are defined by two cells.
gt; gt;
gt; gt; Let's Say I key in 20 in the first look up cell and 3ES in the second
gt; gt; look up cell. The correct answer to the above is 2. That is the first
gt; gt; and the sixth lines meet both criteria.
gt; gt;
gt; gt; Help please
gt; gt;
gt; gt;
gt; gt; --
gt; gt; JohnHill
gt; gt; ------------------------------------------------------------------------
gt; gt; JohnHill's Profile: www.excelforum.com/member.php...oamp;userid=25171
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=542357
gt; gt;
gt; gt;


Gidday Biff ....

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

Ripper Boris !!!--
JohnHill
------------------------------------------------------------------------
JohnHill's Profile: www.excelforum.com/member.php...oamp;userid=25171
View this thread: www.excelforum.com/showthread...hreadid=542357Try 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:

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

mcgimpsey.com/excel/formulae/doubleneg.html

Biff

quot;JohnHillquot; gt; wrote in
message ...
gt;
gt; Gidday Biff ....
gt;
gt; Thanks for that ... can you tell me what does the -- do ??
gt;
gt; Ripper Boris !!!
gt;
gt;
gt; --
gt; JohnHill
gt; ------------------------------------------------------------------------
gt; JohnHill's Profile:
gt; www.excelforum.com/member.php...oamp;userid=25171
gt; View this thread: www.excelforum.com/showthread...hreadid=542357
gt;