Broken cross-sheet formula

Chris Marsh

I have a cross-sheet formula that reads from nine separate sheets. It's a COUNTIFS formula that is designed to count the number of times a combination of options occurs across three columns - a select from a dropdown text column, a date column, and a checkbox column.

If a user selects, say two of those options, but not all three, it breaks the cross-sheet formula.

I don't know how to remedy that.

Does anyone have any ideas?

prime_nathaniel

@Chris Marsh please provide specifics on your formula, dataset, and data model. You can use generic values if sensitive info. You can also model the whole thing on one sheet for your use case as cross references should not have bearing on the solution. You described what isn't working not what should happen with the business rules you are trying to model.

"count the number of times a combination of options occurs across three columns - a select from a dropdown text column, a date column, and a checkbox column."

This phrase specifically needs to be a more fleshed out use case for a guaranteed answer. What is "combination of options", I assume this means you want to count against three columns but with what test criteria is not clear.

ex dropdown equals refA, date = today(), and checkbox = true would help some. Even so that wouldn't cover 9 cross references so would seem this cannot be the case intended.