Help with checkbox formula

Malissa M Alexander

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)

Paul Newcome

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)

Malissa M Alexander

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

Paul Newcome

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

Malissa M Alexander

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

Paul Newcome

So it is a date and not just a number?

Malissa M Alexander

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.

Paul Newcome

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

Malissa M Alexander

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

Malissa M Alexander

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

Paul Newcome

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

Malissa M Alexander

They are each covering a separate column

The other sheet

Paul Newcome

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.

Malissa M Alexander

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?

Paul Newcome

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.

Malissa M Alexander

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