Count if formulas

Amanda Thacker

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

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!