# How to make a COUNTIFS Formula work

Options
✭✭✭✭✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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".

• ✭✭✭✭✭✭
Options

@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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 03/10/21
Options

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. 😂

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Fantastic! Glad it worked for you.

Best,

Heather

• ✭✭✭✭✭✭
Options

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?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!