# Hello! On calc, how do I count the cells of range "X" that contain the exact data with cells in range "Y"? [closed]

So, I have two columns that I want to compare for matching data (numbers or text) and I want to count the duplicates. Like 5 cells from column A have the same data with cells in column B. Thank you Yanis

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by yanismwa close date 2018-12-09 12:56:44.654749

Sort by » oldest newest most voted

Hello, @yanismwa

Thanks a lot for your question. You can use COUNTIF function for this purpose. If you have data starting from 1st row in columns A and B, then in column C you can insert formula =COUNTIF(B:B;A1) and the formula will return how many cell in the whole B column have the same value as in A1 cell. Then just fill C column down with this formula as far, as needed.

Please also find an example attached.

more

Thank you for the answer, it works! How can I count all of them at once with one formula? So instead in the formula to have cell A1 and next formula A2 and so on, to put the entire A column and this way use just one formula and only. (example =COUNTIF(B1:B1000;A1:A1000), this formula of course doesn't work). Thanks again.

( 2018-12-08 16:30:00 +0200 )edit

It works if you enter it as array/matrix formula, i.e. close the input with Shift+Ctrl+Enter instead of just Enter.

( 2018-12-08 16:58:56 +0200 )edit

If you want to fill cells down with first row's formula without dragging down the selection, enter an array formula as @erAck proposed. If you need to sum all the cells with COUNTIF formula and get the final result in one single cell, you can use =SUM(COUNTIF(B1:B1000;A1:A1000)) entered as an array formula or =SUMPRODUCT(COUNTIF(B1:B1000;A1:A1000)) as usual formula.

( 2018-12-08 17:31:51 +0200 )edit

Thank you @SM_Riga!! That was exactly what I was looking for, it does exist and you gave the solution! My compliments!

( 2018-12-08 19:15:35 +0200 )edit

@yanismwa One problem that I have when using =COUNTIF(B:B;A1). It can't find cells that start with "*". I can't change the cell contents, so is there a workaround?

( 2019-06-03 21:25:35 +0200 )edit
1

You probably have either Wildcards or Regular Expressions in formulas enabled. If Wildcards (default setting for Excel interoperability), then you need to escape the * asterisk in the *... search string with a ~ tilde, so ~*.... If RegExp then you need to escape it with a \ backslash as in \*.... The current setting whether Wildcards or RegExp is enabled can be seen under menu Tools -> Options -> Calc -> Calculate, General Calculations.

( 2019-06-04 16:26:25 +0200 )edit