Help with checkbox formula

Paul Newcome

When you say it didn't work... Is it the same error or incorrect results?

Malissa M Alexander

Incorrect Argument Set again

Paul Newcome

Try deleting the formula entirely. Then re-enter the CONTAINS version into a cell in a different column.

Malissa M Alexander

I am just trying to show how many locations submitted a form response each month, maybe there is a different way to summarize this?

Paul Newcome

Are you able to provide a screenshot of the formula within the sheet similar to the screenshot below?

Malissa M Alexander

Paul Newcome

How exactly are you creating your cross sheet references?

Malissa M Alexander

I'm sorry, I don't quite understand your question. I am fairly new to SS. I created a grid that has data submitted via form. This sheet/form includes their site location in one column and the date they submitted in another (as well as other information).

I then created a separate grid sheet to summarize data from that sheet. So far I am calculating how many responses are submitted, how many times each site has submitted a response etc. I do that by putting in the formula and then selecting ranges from my first sheet. What I need now is to know if ALL sites have submitted a response each month. I need to show the percentage of overall completion.

I think the problem with the formulas so far has something to do with multiple entries per site.

Here is an example of my logic counted manually...

Jan 2020, Total Sites=16

SiteA- 2 entries

SiteB- 4 entries

SiteC- 0 entries

SiteD- 7 entries

Site E- 0 entries

_________________________

Total-13 entries

Total sites entered- 3

3/16 - 18.75% complete for the month

Paul Newcome

When you select your ranges for your cross sheet references in the formulas, exactly how are you selecting the ranges? Exactly what do you click on? Are you selecting the cells in each column, or are you clicking on the column header to select the entire column?

Malissa M Alexander

Oh, so I am selecting the cells within the range, like this-

Paul Newcome

Ok. Try clicking on the column header instead. This will automatically select the entire column and include new rows as they are added and will not be affected by rows being deleted or moved around.

Malissa M Alexander

Ok, but the problem with that is then it pulls data from the whole sheet, entries from 2019, 2020 & 2021- I need entries specific to the year and month.

Paul Newcome

That is something that can be built into your COUNTIFS to filter.

COUNTIFS({Year Column}, 2020, {Month Column}, 1, {Text Column}, CONTAINS("specific text", @cell))

Malissa M Alexander

=IF(COUNTIFS({Year} = "2020", {Month} = "1", {Sites}, CONTAINS("RSOC" > 0, 1, 0)))

Paul Newcome

Now we are down to a minor syntax issue...

=IF(COUNTIFS({Year}, 2020, {Month}, 1, {Sites}, CONTAINS("RSOC", @cell)) > 0, 1)