Sumifs Help with Month/Year and checkbox

10/31/19 Edited 12/09/19

I keep getting an error when writing this formula.  Can someone please help?

I want to sum the "Weighted Range" when "Prob Closing" is checked (1), and "Close Date" is January (1) of 2020.

The 3 columns are in another spreadsheet.  

=IFERROR(SUMIFS({Weighted Range}; {Prob Closing}, 1; {Close Date}; MONTH(@cell) = 1; {Close Date}; YEAR(@cell) = 2020); 0))

Popular Tags:

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    It looks correct.

    What error message are you getting?

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    To add:

    Do you only want it to SUM if the date is precisely January 1, 2020?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    between criteria range 1 and criteria 1 is a comma instead of a semicolon. You also have an extra closing parenthesis on the end. Try swapping out the comma for a semicolon and removing that last closing parenthesis, and it may fix your issue.

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Paul,

    Good catch! I missed that!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I think the only reason I caught the comma is because of the use of semi-colons. I use commas, so when I am looking at a formula using semi-colons, it slows me down a lot (which helps me notice the little oddities).

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    makes-sense

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.