The Google Sheets COUNTIF function, which returns a count of elements that match a criterion can also be used to compare two lists. Use conditional formatting to highlight items that are in one list but not in the other.

Here's the format of the COUNTIF function:

=COUNTIF(range, criterion)

The COUNTIF Google Sheets function returns a count of elements in the range that fulfill the criterion. COUNTIF returns 0 if it doesn't find an element; the function returns 1 or a value greater than 1, if it finds element(s) that fulfill the criterion.

To compare two lists, however, with COUNTIF, you will have to specify a range, which will be the first list. Then, you've to specify a criterion, which will be the first cell in the second list. Next, copy the COUNTIF function down till the last cell of the lists. Note that the two lists should be of the same 'height'. That is, both the lists should have the same number of cells.

Be sure to use an absolute cell address or name a range and use it in lieu of cell address. Otherwise, the result will be incorrect, because the range will change, if its address is relative and not absolute, as you copy the COUNTIF formula down the cells till the last cell of the lists.

Let's look at an example to use Google Sheets COUNTIF function to compare two lists:

Store A Store B
------------ ------------
Pencil HB Pen
Clip Pencil
Eraser Paper
Notepad Eraser
Pin Notepad
Paper Clip
Notebook Ruler
Drawing board Rubber band
Envelope Calculator

Say you want to find out if items in Store B are also in Store A. Assume the range, for Store A, starting with the first item (Pencil HB) and ending at the last item (Envelope), is A2:A10. The cell address of the first item (Pen) in Store B is B2.

The COUNTIF formula for this task is:

=COUNTIF(A2:A10, B2)

Type the above formula, say in cell C2. Copy the formula down till cell C10.

Assuming there are no duplicate elements in either list, the Google Sheets COUNTIF function returns 0, if an item in Store B is not in Store A or returns 1, if an item in Store B is also in Store A.

Now, let's use conditional formatting to highlight those items in Store B not in Store A.

Select the range B2 to B10: Click cell B2. Shift-click cell B10. Click Format, Conditional formatting. Click "Custom formula is". In the field type the formula, =COUNTIF(A2:A10, B2)=0. This formula tells Google Sheets to highlight cells with the default color, if the formula returns 0. You can change the color to one of your choice.

Please take a look at this video tutorial, which gives the steps to compare two lists using the COUNTIF Google Sheets function, and use conditional formatting, with an example.

свернуть