Count if formulas

Hello,

I have a fairly simple formula that I need assistance with. I have a 'Date completed' column (UK Date format) and a check box column, 'Test1'. I want to do a count in a separate cell for all the rows that have check boxes ticked for within specified month period. E.g. Count the number of test completed in May. I will be linking to a separate sheet but should be able to work that in with the assistance of smartsheet.

Thank you

Amanda

Find more posts tagged with

Sheets

Formulas

Cell linking

Comments

Paul Newcome

Linking from a different sheet, the formula would look something like this...

=COUNTIFS({Data Sheet Checkbox Column}, 1, {Data Sheet Date Column}, IFERROR(MONTH(@cell), 0) = ##)

where you would just have to change the ## to the appropriate month number you are looking for.

Amanda Thacker

Hello Paul,

Thank you for your response however this is not working when i reference the cells require, have i made an error somewhere?:

=COUNTIF(Health Checks Range 2}, 1, ({Health Checks Range 15},IFERROR(MONTH(@cell), 0) = 05)

'HealthChecks Range 2' is the check box column and 'Health Checks Range 15' is the date column (I have checked the references for these columns are correct). The date format is showing as 23-May-2019 if that makes any difference.

Many thanks for your assistance.

Andrée Starå

Hi Amanda,

You missed an S for COUNTIFS. Try this.

=COUNTIFS(Health Checks Range 2}, 1, ({Health Checks Range 15},IFERROR(MONTH(@cell), 0) = 05)

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Amanda Thacker

Thank you. Just need a bit of tweaking with the brackets but now works: =COUNTIFS({Health Checks Range 2}, 1, {Health Checks Range 15}, IFERROR(MONTH(@cell), 0) = 5)

Andrée Starå

Excellent!

Happy to help!

Sorry about the missing the brackets.

Best,

Andrée

Paul Newcome

Glad we were able to get it working for you!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?