Sumifs Adding multiple criteria

03/27/20
Accepted

Good morning,

I am trying to add up total hours of a {Project Type} Column if the project type contains the following: Implementation, Implementation - Small Market, and ELD Implementation


=SUMIFS([Hours Worked]:[Hours Worked], Date:Date, >=TODAY(-7), Project Type:Project Type, "Implementation", "Implementation - Small Market", "ELD Implementation"

I have tried to double check Parantheses and commas but am getting unparseable message

Best Answers

  • Debbie SawyerDebbie Sawyer ✭✭✭
    Accepted Answer

    @Paul Newcome Thanks for the logic in combining with an OR function. I had tried a few ways and couldn't get it to work! :)

    Happy to learn too! @bradleyesmith86431 thanks for the post :)

    Kind regards

    Debbie

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    @bradleyesmith86431 Try this...

    =SUMIFS([Hours Worked]:[Hours Worked], Date:Date, >=TODAY(-7), [Billable Customer Hours]:[Billable Customer Hours], @cell = 1)

Answers

  • Hi Bradley

    I would do this in two stages:

    1) I'd set up a helper column called SumProjType as a checkbox with the formula of:

    =IF(OR([Project Type]@row ="Implementation",[Project Type]@row ="Implementation - Small Market",[Project Type]@row ="ELD Implementation"),1,0)

    2) then I'd set up the =SUMIFS formula as:

    =SUMIFS([Hours Worked]:[Hours Worked], Date:Date, >=TODAY(-7), [SumProjType]:[SumProjType],1)

    With syntax

    Function Arguments are contained in ( ) e.g. =SUM(range)

    Cross Sheet References are contained in { }

    Column Names that contain a Space or end in a Number are contained in [ ]

    Hope this helps.

    Good luck

    Debbie

  • bradleyesmith86431bradleyesmith86431 ✭✭✭✭✭

    Thanks Debbie for this response and suggestion!


    Would there be a way that you could do it without the checkbox column? The reason being, I am having the implementation managers fill out a form that goes to the sheet.

    It would be one less thing, an implementation manager would have to remember to check....

    I was just curious if there was a way to do it without creating a SumProjType as a checkbox..


    Thank you so much for the help and feedback.

  • bradleyesmith86431bradleyesmith86431 ✭✭✭✭✭

    Awesome Paul,


    That worked spectacularly! Thank you so much!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️


    Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

  • Debbie SawyerDebbie Sawyer ✭✭✭
    Accepted Answer

    @Paul Newcome Thanks for the logic in combining with an OR function. I had tried a few ways and couldn't get it to work! :)

    Happy to learn too! @bradleyesmith86431 thanks for the post :)

    Kind regards

    Debbie

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Debbie Sawyer Sure thing. 👍️

  • bradleyesmith86431bradleyesmith86431 ✭✭✭✭✭

    When Trying to SumIf a "Checkbox Column" in the last 7 days would this be the proper formula?

    =SUMIFS([Hours Worked]:[Hours Worked], Date:Date, >=TODAY(-7), [Billable Customer Hours]:[Billable Customer Hours], "Yes")


    Or is it "True"


    for some reason the formula is not summing the Hours worked when Checkbox is check.


    Thanks.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    @bradleyesmith86431 Try this...

    =SUMIFS([Hours Worked]:[Hours Worked], Date:Date, >=TODAY(-7), [Billable Customer Hours]:[Billable Customer Hours], @cell = 1)

  • bradleyesmith86431bradleyesmith86431 ✭✭✭✭✭

    I forgot the @cell = 1


    This worked great!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @bradleyesmith86431 Happy to help. 👍️

Sign In or Register to comment.