How is the checkbox function recognised?

Phil Lomas

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

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

or

[Column Name]:[Column Name]; 1

.

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