# Help with checkbox formula

I am trying to make a checkbox on one sheet change depending on two criteria on a separate sheet. The first sheet has data showing drills submitted by location and date. My goal is to show completion on a monthly basis. ex: 10/12 sites completed drill in January

I need the other sheet to show completion for each location and each month.

If sheet a says "this location" and the month equals "1", checkbox should be marked.

This is the formula I have been trying and changing with no luck.

=IF({Site 3}, "RSOC-Fremont, California",{Month}@row,"1", 1, 0)

«13

• ✭✭✭✭✭✭

You are going to first want to count how many times your criteria is met and then say that if that count is greater than zero to check the box.

=IF(COUNTIFS({Site 3}, "RSOC-Fremont, California",{Month}, "1") > 0, 1)

• @Paul Newcome this is helpful, but still giving me INCORRECT ARGUMENT SET, I'm stumped

• ✭✭✭✭✭✭

What type of data is in your {Month} range?

• My Month range is a formula to number the month based on entry date (1-12)

• ✭✭✭✭✭✭

So it is a date and not just a number?

• In sheet 1 I have an array of different information. one column has the date, another column is set up to number the month, based off that date.

In a separate sheet, I am trying to track how many locations have submitted a response each month going back 3 years.

• ✭✭✭✭✭✭

Can you copy/paste the exact formula you are getting the incorrect argument error for?

• =IF(COUNTIFS({Site 3}, "RSOC-Fremont, California", {Month}, "1") > 0, 1)

• Maybe I'm approaching it all wrong and should find another way to collect this data from the main sheet?

• ✭✭✭✭✭✭

How are you setting up your cross sheet reference ranges? Are they both covering a single column?

• They are each covering a separate column

The other sheet

• ✭✭✭✭✭✭

At this point I would have to double check your ranges in your cross sheet references. The formula that you posted above should not be throwing an error. The only other thing that could be would be that your ranges do not match or that error is present within one of the ranges.

• I have double and triple checked that this is not the case. Would it be better if I used a totally different formula? Something that will check the box if a site name appears between two dates?

• ✭✭✭✭✭✭

Let's see if removing the quotes from around the number makes a difference. It shouldn't but it is at least worth a try...

=IF(COUNTIFS({Site 3}, "RSOC-Fremont, California", {Month}, 1) > 0, 1)

I also wonder if maybe it has to do with the comma in "RSOC-Fremont, California". Try this...

=IF(COUNTIFS({Site 3}, CONTAINS("RSOC-Fremont", @cell), {Month}, 1) > 0, 1)

NOTE: we removed the text from the comma on so it is searching for the text "RSOC-Fremont". In this case the comma is after the second quote and before the @cell reference.

• This didn't work either :( I chewed on it all night and think I might need to go back to the drawing board

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!