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

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.

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.

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

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.

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

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

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