How to make a COUNTIFS Formula work

SteCoxy

Hello, I'm hoping someone might be able to help me make a COUNTIFS formula work. Some background... I've set up a sheet that aims to capture a team's availability of staff in any given month, should their be a major event occur. The staff would complete a form that asks for their name, contact details, job role and their availability for that month - each form/sheet has columns for each day of the month and then they answer Yes/No/Maybe to each day of the month.

I'm trying to set up a COUNTIFS formula in the sheet summary to work out the total number of staff for each role for the whole month. I've done a screenshot of the sheet for your reference below:

I thought the formula would work as follows:

=COUNTIFS([Role:Role], "Audio News Domestic", [1st February]:[28th February], "Yes")

but it doesn't seem to like it, as it's coming up with an UPARSEABLE error message.

Any ideas how I can get it to work?

Heather Duff

@Andrée Starå Thanks for the assist!

@SteCoxy , one idea would be to add a helper checkbox column (which could be hidden) that has a formula like this in it:

=IF(COUNTIF([1st february]@row:[28th february]@row, "Yes") >0,1,0)

This will check the box if any of the cells in Feb 1 - Feb 28 have yes in them. We'll call this column Helper.

From there, you could use the following formula in your summary:

=COUNTIFS([Role]:[Role], "Audio News Domestic", [Helper]:[Helper], 1)

This will then count the number of times the helper column has a checkmark AND the Role column has Audio News Domestic.

Let me know if it works!

Best,

Heather

Heather Duff

Hi! You're close - try this!

=COUNTIFS([Role]:[Role], "Audio News Domestic", [1st February]:[28th February], "Yes")

At first glance, it just looks like your brackets were set as [Role:Role], when it actually needs to be [Role]:[Role] (or, really, it could just be Role:Role, because column titles with no space in them do not need brackets).

Hope this helps!

Best,

Heather

SteCoxy

Hello, thank you for getting back to me regarding this. I've just tried that but a new error message now appears: "INCORRECT ARGUMENT SET".

Heather Duff

@SteCoxy How strange! I noticed in your screenshot that there's an end parenthesis after "Audio News Domestic". If that's still in there, will you please remove it? That may be the problem.

SteCoxy

Hello, I've tried doing this, but unfortunately, it's not liking it still. It's coming up with the UNPARSEABLE error message again.

Heather Duff

Hi! Try adding a comma after Domestic" and removing the extra parenthesis at the end. Your formula should read:

=COUNTIFS([Role]:[Role], "Audio News Domestic", [1st February]:[28th February], "Yes")

I just realized there may be another issue, though. I just realized that you're searching within multiple columns (1st February:28th February). It may require a little more thought if the above formula doesn't work.

@Andrée Starå? @Paul Newcome? Tagging in people who actually know what they're talking about. 😂

SteCoxy

Thanks for all your efforts here Heather, but unfortunately you're right that it's going to need more thought, as it didn't work. Any further guidance will be much appreciated!

Andrée Starå

Hi @SteCoxy

I hope you're well and safe!

@Heather D Thanks for the vote of confidence.

Yes, that won't work because the ranges in a COUNTIFS formula have to be the same size.

You'd need to use multiple COUNTIFS and add them together or add so-called helper columns, so the ranges match.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Heather Duff

@Andrée Starå Thanks for the assist!

@SteCoxy , one idea would be to add a helper checkbox column (which could be hidden) that has a formula like this in it:

=IF(COUNTIF([1st february]@row:[28th february]@row, "Yes") >0,1,0)

This will check the box if any of the cells in Feb 1 - Feb 28 have yes in them. We'll call this column Helper.

From there, you could use the following formula in your summary:

=COUNTIFS([Role]:[Role], "Audio News Domestic", [Helper]:[Helper], 1)

This will then count the number of times the helper column has a checkmark AND the Role column has Audio News Domestic.

Let me know if it works!

Best,

Heather

SteCoxy

Hello, apologies for the delay in responding here - I've had a busy couple of months!

Yes, this solution has worked thank you - I've then added a totals row at the top, so it counts how many "yes" responses have been received per day.

Heather Duff

Fantastic! Glad it worked for you.

Best,

Heather

SteCoxy

Just wanted to ask you both @Andrée Starå & @Heather D whether this method for capturing people's availability is the best method or is there a more streamlined and more joined up way of doing it?

Just to remind you, the workflow is:

I've set up a sheet that aims to capture a team's availability of staff in any given month, should their be a major event occur. The staff would complete a form that asks for their name, contact details, job role and their availability for that month - each form/sheet has columns for each day of the month and then they answer Yes/No/Maybe to each day of the month. There's a sheet for each month of the year and each sheet has the according number of days as columns.

Would there be a better way of doing this?