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 tocriteria1
is the criteria that cells inrange1
must meetrange2
,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.