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

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.