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.