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.

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?

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.

Stats

Asked: 2018-12-08 00:30:14 +0200

Seen: 43 times

Last updated: Dec 08 '18