Will SUMIFS work with a date and a checkbox?

I am trying a SUMIFS formula without much success. The formula needs to return the Distance if Completed Date is before 31/03/20 and Signs has been ticked/checked.

Distance (number)

Completed date (date)

Signs (checkbox)

Is this possible?

Thanks

Faz

Find more posts tagged with

Formulas

Comments

Chak Khiam

It is and the following works:

SUMIFS(Distance:Distance, [Completed Date]:[Completed Date], <DATE(2020,3,30), Signs:Signs, 1)

Does it work in your case?

Faz Mussa

Hi Chakm, I'm afraid not - it says #UNPARSEABLE

Andrée Starå

Hi Faz,

Chak's formula should work. Try my second one and see if that works.

Try something like this.

=SUMIFS(Distance:Distance; [Completed Date]:[Completed Date]; <DATE(2020; 3; 30); Signs:Signs; 1)

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

=SUMIFS(Distance:Distance, [Completed Date]:[Completed Date], <DATE(2020, 3, 30), Signs:Signs, 1)

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

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Faz Mussa

I re-did Chak's formula and it worked. I think the issue was because the column was labelled Distance (miles) - the extra brackets were probably confusing the formula. Column renamed (without brackets ).

Thanks so much Chak (and Andree too).

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…