Introduction

Although I haven’t been overly impressed with ChatGPT as a creative force, I have to say that I am impressed with it as an alternative to Google.

This afternoon I was trying to figure out how to do something in Google Sheets. No matter how I framed the query in Google, it came up with results I found somewhat unhelpful. Either they were overkill in terms of what I wanted to achieve, or they led to a YouTube video, which I didn’t want to sopend time watching, or they led to someoine trying to sell me some kind of souped-up add-on. I wasted at least half an hour on this quest.

So I typed the query into ChatGPT and it gave me exactly what I was looking for in seconds. Here’s what it said:

My query

In google sheets, how can I add up the number of items in column A that meet a specific criterion AND have the corresponding box in column B tickedin google sheets, how can I add up the number of items in column A that meet a specific criterion AND have the corresponding box in column B ticked.

ChatGPT’s answer

To add up the number of items in column A that meet a specific criterion AND have the corresponding box in column B ticked in Google Sheets, you can use the COUNTIFS function.

The COUNTIFS function allows you to specify multiple criteria to count the number of cells that meet all the criteria.

Here's the syntax for the COUNTIFS function:

Copy code

COUNTIFS(range1, criteria1, [range2, criteria2, ...])

• range1 is the range of cells that you want to apply the first criteria to

• criteria1 is the criteria that cells in range1 must meet

• range2, criteria2, etc. are optional additional ranges and criteria that you can specify

For example, suppose you have a sheet with the following data:

To count the number of items in column A that are "Apples" and have the corresponding box in column B ticked, you can use the following formula:

Copy code

=COUNTIFS(A:A, "Apples", B:B, "X")

This formula will return the result 2, because there are two rows that meet both criteria (rows 1 and 3).

You can use the COUNTIFS function with any criteria that you want. For example, to count the number of items in column A that are not "Apples" and have the corresponding box in column B ticked, you can use the following formula:

Copy code

=COUNTIFS(A:A, "<>Apples", B:B, "X")

This formula will return the result 1, because there is only one row that meets both criteria (row 4).

I tried it out on my own spreadsheet, and it was just what I wanted. Now, I had already worked out that what I needed was probably the Countifs function or, possibly, the AND function. But as I said, the sites that I found which purported to explain how to use it were not exactly easy to apply to my own needs. Thinking about it, the reason the ChatGPT solution worked so well was that it was, in effect, custom-made as an answer to my specific query. I didn’t have to try and translate a complicated setup devised for sales managers into my relatively simple requirement of an article record.