Sumifs Adding multiple criteria

bradleyesmith86431

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

Paul Newcome

You can use an OR statement to combine multiple criteria for the same range (I also added square brackets around the Project Type column names).

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

Debbie Sawyer

@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 Newcome

@bradleyesmith86431 Try this...

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

Debbie Sawyer

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

bradleyesmith86431

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.

Paul Newcome

You can use an OR statement to combine multiple criteria for the same range (I also added square brackets around the Project Type column names).

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

bradleyesmith86431

Awesome Paul,

That worked spectacularly! Thank you so much!

Paul 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 Sawyer

@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 Newcome

@Debbie Sawyer Sure thing. 👍️

bradleyesmith86431

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 Newcome

@bradleyesmith86431 Try this...

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

bradleyesmith86431

I forgot the @cell = 1

This worked great!

Paul Newcome

@bradleyesmith86431 Happy to help. 👍️