How is the checkbox function recognised?

I'm trying to output the number of sick-days into a cell using the criteria 'email address' and checkbox ticked as i only want it to recognise the sickdays if checkbox is ticked. Don't really understand how the checkbox is recognized as I am getting a '#invalid operation'

=SUMIFS([Is your absence related to a disability]:[Is your absence related to a disability] =1, [Your email address]:[Your email address], "phil.lomas@company.org", [Number of sick days]:[Number of sick days])

Can anyone put me on the right path?

Thankyou

Find more posts tagged with

Human Resources

Non-profit

Formulas

Comments

Andrée Starå

Hi Phil,

Try something like this.

=SUMIFS([Number of sick days]:[Number of sick days]; [Is your absence related to a disability]:[Is your absence related to a disability]; 1; [Your email address]:[Your email address]; "phil.lomas@company.org")

The same version but with the below changes for your and others convenience.

=SUMIFS([Number of sick days]:[Number of sick days], [Is your absence related to a disability]:[Is your absence related to a disability], 1, [Your email address]:[Your email address], "phil.lomas@company.org")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Hope that helps!

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Phil Lomas

Yes that does it. :-)

Andrée Starå

Excellent!

Paul Newcome

To further explain...

SUMIFS has a different syntax than SUMIF. The range to be summed comes first in a SUMIFS.

When writing it out, the range and the criteria need to be separated with a comma or semicolon depending on your region. So instead of having this:

[Column Name]:[Column Name] = 1

you would need to use this:

[Column Name]:[Column Name], 1

[Column Name]:[Column Name]; 1

in relation to your original question, a box being checked can either be referenced with true or 1.

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.