Duncan's Diurnal Diatribe: Matching Cells in a Range

Introduction

Someone on Quora asked this question: "How can I find the total number of matching cells in two Excel columns which specifically contain a text value that I require?" and then they deleted it. Well, I had written an answer before I learned it had been deleted and, of course, I decided to post it here.

chatGPT

I will admit that I used this example to post to chatGPT.ai to see what it would suggest and then I added more.

chaptGPT said this:

To find the total number of matching cells in two Excel columns that specifically contain a text value you require without using the Oxford comma, you can use the COUNTIFS function.

The COUNTIFS function allows you to count the number of rows meeting multiple criteria in different columns.

Here's the syntax for the COUNTIFS function:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

For example, let's say you want to count the number of rows where column A contains the text value "Apple" and column B contains the text value "Red". Assuming your data is in the range A1:A10 and B1:B10, use the following formula:

=COUNTIFS(A5:A54, "Apple", B5:B54, "Red")

This formula counts the number of rows where column A contains "Apple" and column B contains "Red" in the same row.

Replace the placeholders with your own ranges, criteria, and cell references as needed.

chatGPT Results

Here are the results of the chatGPT suggestions

To do this, I extended the input range to show 50 Fruit and Colour choices and note, there are many duplications in this range

Still, in cell E6, the formula uses cells E4:E5 to tell us that there are 10 yellow bananas in the list. Just change E4 to apple or pear, for example and Yellow to red or green to find the number of red apples etc in the list

E6=COUNTIFS(A5:A54,E4,B5:B54,E5)

My Suggestions

I extended the example to illustrate the total number of different types of fruit, the total number of reds and greens ...

That's fine, of course but it does not give us the full picture of the possible combinations of fruit and colour, The following screenshot shows us that: