Formula not working on one data base but does on others

Michelle McN

Hi

I am using the following formula to count the number of people who have declined a training invitation:

=COUNTIFS({Training Sessions Registration and RSVPs Range 5}, "Training Session", {Training Sessions Registration and RSVPs Range 4}, "Organisation", {Training Sessions Registration and RSVPs Range 3}, >=DATE(2023, 3, 27), {Training Sessions Registration and RSVPs Range 3}, <=DATE(2023, 3, 31), {Training Sessions Registration and RSVPs Range 6}, "Rejected")

I have created separate data bases for the 5 organisations that are participating in the training. This formula is repeated for each course across 8 different date ranges. The formula works on 4 of the databases across all weeks and courses but not on 1.

I've tried copying in the formula from the other sheets that have successful count results however once it's copied into the database that isn't working the result shows '0' even with the other organisation name & training course still within the formula - which returns a result in it's 'home' database.

I've also remapped the cross sheet references, updated the criteria wording to reflect the reference data base however the formula still does not work.

As the formula is successful on 4 data bases I'm thinking that the formula is not the issue but that there is some sheet formatting on this one data base that has been inadvertently applied (either by me hitting the wrong keys or my cat walking across the keyboard). I'd rather not have to recreate the database as it has 94 columns to create, which as we can't copy columns across is quiet a lot of work to set up again.

Can anyone please advise what the issue could be and how I would go about resolving it.

Thanks

Michelle

Kelly Moore

Hey @Michelle McN

When trouble-shooting a formula with multiple criterion that is producing an unexpected results (vs an error), try eliminating the criteria one by one for the formula of your non-working database. Or start with just one criteria and add in the the others, one by one. You're looking for the criteria that is causing the zero value. For example, it could be a different spelling in one of the databases. Let me know what you find.

Kelly

Michelle McN

Hi Kelly

Thanks for the response.

I don't believe the formula is the issue. In this particular date range the answer should be '2'. I've updated the criteria wording in the formula to match the data base and it still returns a '0'.

Also when I copy the formula from another organisation were the formula is working and returning a calculated value it shows '0' immediately on the issue data base while still using the other organisations criteria wording. It is this that has me wondering if there is a formatting issue in the database. How can the same formula (no changes) work on one sheet but not the other.

Michelle

Kelly Moore

Hey @Michelle McN

The formatting was exactly what I was wanting you to test by eliminating criteria. You would be able to narrow down the search.

Try this

=COUNTIFS({Training Sessions Registration and RSVPs Range 5}, "Training Session", {Training Sessions Registration and RSVPs Range 4}, "Organisation", {Training Sessions Registration and RSVPs Range 3},ISDATE(@cell))

Michelle McN

Hi Kelly

I unsure why the formula needs to change when it works the other 70 times it is applied for this date range across the the other four data bases.

Apologies for sounding rude but I've created 5,000 formulas based on the above format across the response criteria's, date ranges, courses and organisations and of those formulas only 8 don't work on this one data base (the other 600 is this data base using the same format do work). I also don't want to change the format of these 8 away from the others as it has the potential for causing issues later on.

The same formula on the other data bases does work, it only when copied into a cell on this one data base that it does not work. It's why I believe it's a cell formatting issue rather than a formula issue however I'm unable to work out what that cell formatting issue is.

Michelle