# Counting exact values in two columns

#### MrOrb

##### New Member
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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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

=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

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

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
ABCDEFGHI
1L1L2L3L4L5Column L1Column L2No of Times
222237123
312346
411145
511456
633456
712235
822445
912345
Match Pairs

Peter_SSs,
Perfect, thank you very much.

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

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

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

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

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

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

Replies
1
Views
155
Replies
6
Views
197
Replies
1
Views
423
Replies
32
Views
634
Replies
3
Views
150

1,202,990
Messages
6,052,947
Members
444,620
Latest member
marialewis16

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back