How do you add 2 SUMIFS in same function or SUM of Range based on 2 values in SUMIFS Criteria Range?

Robert Tebault

I have a schedule that has locations in different regions and a install status picklist. I need to create a sheet summary function that calculates a numerical value from one column based on the region and the status. I have two SUMIFS formulas that work separately but I can not add them together nor can I get the logic of AND,OR,CONTAINS to work when nested within the function. Here is what I have so far:

=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Complete", Region1:Region235, "Carolina") {Works}

=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Completed w/ Conditions", Region1:Region235, "Carolina") {Works}

=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Complete", Region1:Region235, "Carolina") + SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Completed w/ Conditions", Region1:Region235, "Carolina") {Does NOT Work}

As you can see, I need to add the value in [Actual # of Cams]1:[Actual # of Cams]235 based on the the Region and the two Completion Statuses combined. Has anyone tried to do something similar before and have any advice on where the syntax is broken?

Robert Tebault

Fixed! Had the criteria in the wrong order. See below:

=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, Region1:Region235, "Carolina", [Install Status]1:[Install Status]235, "Complete") + SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, Region1:Region235, "Carolina", [Install Status]1:[Install Status]235, "Completed w/ Conditions")

Leibel S

try:

=SUM(SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Complete", Region1:Region235, "Carolina"), SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Completed w/ Conditions", Region1:Region235, "Carolina"))

Robert Tebault

@Leibel S Thank you for the help. Unfortunately it doesn't completely work. When applied it calculates only this portion of the string: [Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, "Complete", Region1:Region235, "Carolina")

Leibel S

Try:

=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, [Install Status]1:[Install Status]235, or(@cell="Complete", @cell="Completed w/ Conditions"), Region1:Region235, "Carolina")

Robert Tebault

Fixed! Had the criteria in the wrong order. See below:

=SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, Region1:Region235, "Carolina", [Install Status]1:[Install Status]235, "Complete") + SUMIFS([Actual # of Cams]1:[Actual # of Cams]235, Region1:Region235, "Carolina", [Install Status]1:[Install Status]235, "Completed w/ Conditions")