0

I am unsure of the use of COUNTIFS with two variables while excluding blank cells from being counted in this scenario, wanted to see if there would be a more viable solution. I am relatively new to Google Sheets, please forgive me if the question seems simple. For context, I have two worksheets:

Sheet1 contains Form Responses in the following columns:

A: Timestamp
B: Name
C: Food

In Sheet2 Column A, I have each possible answer that can appear in Sheet1 Column B. In the first row of Sheet2 starting in B1, I have each possible answer that can appear in Sheet1 Column C. These act as data headers. These are the headers of this table.

In the cells between them, I am using a COUNTIFS formula:

=COUNTIFS('Form Responses 1'!$B:$B,Sheet2!$A2, 'Form Responses 1'!$C:$C,"*"&Sheet2!$B$1&"*")

This formula checks the contents of cell A2 on Sheet2 against the values that may appear in Column B on Sheet1 and checks the contents of column C on Sheet1 against the cell contents of cell B1 on Sheet2 using an intentional * wildcard to count all cells in Sheet1 Column C that contain the text in Sheet2 B1.

My issue is that in Sheets, it seems to handle the exclusion of blank cells in a different way from Excel. Using methods outlined for similar uses, the addition of "<>" or ">""" would normally do the trick, but seems to fail to do so in Sheets.

I've read that DCOUNTA may be a more worthwhile route to take than COUNTIFS. Considering my intention with this formula, is there a variation of DCOUNTA that can handle multiple variables in the way COUNTIFS can?

1

Try:

=COUNTIFS('Form Responses 1'!$B:$B,Sheet2!$A2,'Form Responses 1'!$C:$C,"*"&Sheet2!B$1&"*")

(One fewer $.)

The Sheet2! are not required.

0

The condition "<>" is a correct form of excluding blank cells in countifs. Example:

=countifs(C:C, ">2", D:D, "<6", E:E, "<>")

counts the rows where C is greater than 2, D is less than 6, and E is nonempty.

-1

I can confirm the weirdness of COUNTIFS.

In general in COUNTIFS, FILTER and similar formulas you can use any scalar function for the condition, using a range in place of the scalar argument. So in case of COUNTIFS and blanks, this should also count blanks:

=countifs(A:A, isblank(A:A))

But things start going south indeed when you start applying nested functions.

=countifs(A:A, not(isblank(A:A)))

does not work reliably for me... Perhaps because of the well known problem with Booleans inside array functions. But

=rows(filter(A:A, not(isblank(A:A))))

does work every time. Strange!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.