Counting exact values in two columns

[MrOrb]

How would I be able to count the amount of times col L1 has a value or 1 and col L2 has a corresponding value 4 only? Or how would I be able to count the amount of times col L1 has a value or 2 and col L2 has a corresponding value 2 only?

OCC SRT
23G
L1 L2 L3 L4 L5



2 2 2 3 7
1 2 3 4 6
1 1 1 4 5
1 1 4 5 6
3 3 4 5 6
1 2 2 3 5
2 2 4 4 5
1 2 3 4 5

 

[andy3816]

use the countif formula

=countif(range, Criteria)

eg =countif(A:A,1) wilol count the entire column a and return a value of the number of times the value = 1

 

[andy3816]

=countif(A:A,1)+countif(A:A,2)

would give you the result you want in column A

you can also use < and > in the criteria

=Countif(A:A,"<3") but this will also return 0 as a counted number

 

[MrOrb]

I'm no sure the responses understand. I'm only interested in being able to count precise pair in two columns..ie:
how many times did each of the following occur, out of the data set in two columns?
col 1 col 2
1 1
1 2
1 3
1 4
....and so on

 

[Peter_SSs]

MrOrb

Welcome to the Mr Excel board!

See if this is the sort of thing you are after. The formula in cell I2 is:
=SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2))

Mr Excel.xls
	A	B	C	D	E	F	G	H	I
1	L1	L2	L3	L4	L5		Column L1	Column L2	No of Times
2	2	2	2	3	7		1	2	3
3	1	2	3	4	6
4	1	1	1	4	5
5	1	1	4	5	6
6	3	3	4	5	6
7	1	2	2	3	5
8	2	2	4	4	5
9	1	2	3	4	5
Match Pairs

 

[MrOrb]

Peter_SSs,
Perfect, thank you very much.

 

[MrOrb]

If your intersted in how the info you supplied is being used, go to

http://www.lotterypost.com/threads122849.htm

It seems, there are more and more people putting your answer to use.

I guess many thanks is in order, rather than just mine.

 

[MrOrb]

What is the "--"? What's it called? I'm unfamiliar with this.

=SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2))

 

[Peter_SSs]

What is the "--"? What's it called? I'm unfamiliar with this.

=SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2))

They are just two minus signs. $A$2:$A$9=G2 returns an array of TRUEs and FALSEs. The -- coerces Excel to convert these TRUEs and FALSEs into 1s and 0s so that they can be multiplied within the SUMPRODUCT.