Count If ?

[JohnHill]

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

[Biff]

Hi!

Try this:

C1 = 20
D1 = 3ES

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

Biff

"JohnHill" <JohnHill.27vv4a_1147754101.3426@excelforum-nospam.com> wrote in
message news:JohnHill.27vv4a_1147754101.3426@excelforum-nospam.com...
>
> 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
>

[Sukhjeet]

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

[simonsmith]

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

[JohnHill]

Gidday Biff ....

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

Ripper Boris !!!

[Sukhjeet]

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

[Biff]

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" <JohnHill.27vycy_1147758300.5389@excelforum-nospam.com> wrote in
message news:JohnHill.27vycy_1147758300.5389@excelforum-nospam.com...
>
> 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
>