Ask Your Question

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

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

yanismwa gravatar image

updated 2018-12-08 00:34:33 +0200

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted

answered 2018-12-08 09:42:10 +0200

updated 2018-12-08 10:10:28 +0200

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.

edit flag offensive delete link 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.

yanismwa gravatar imageyanismwa ( 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.

erAck gravatar imageerAck ( 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.

SM_Riga gravatar imageSM_Riga ( 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!

yanismwa gravatar imageyanismwa ( 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?

TJMcK gravatar imageTJMcK ( 2019-06-03 21:25:35 +0200 )edit

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.

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

Question Tools



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

Seen: 43 times

Last updated: Dec 08 '18