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?
Best Answer
Try:
(One fewer
$
.)The
Sheet2!
are not required.