SumIF using AND OR

Sarah Bystrom

Hi,

I was able to do a simple SUMIF but I need to add in ands and couldn't figure it out with SUMIF or SUMIFS. Here is what I have right now that is "INPARSEABLE"

=SUMIF({Merkle - Retainer Hours Log Range 1}, CONTAINS(Resource@row, @cell), {Merkle - Retainer Hours Log Range 2}), OR(SUMIF({Gyro Retainer Actual Hours Range 1}, CONTAINS(Resource@row, @cell), {Gyro Retainer Actual Hours Range 2}), AND {Gyro Retainer Actual Hours Range 3} OR {Merkle - Retainer Hours Log Range 3}, "September 2021")

Basically I need to look up one row to see if that value matches the other sheet than sum from a different column. I need to do this from two different sheets to consolidate the data. And I need to then say it has to be from the month of September.

Thanks,

Kimberly Loveless

I'm going to be honest I am not positive this one is going to work but I am going to try....

=SUM(SUMIFS({Merkle - Retainer Hours Log Range 2},{Merkle - Retainer Hours Log Range 3} ,"September 2021",{Merkle - Retainer Hours Log Range 1}, CONTAINS(Resource@row, @cell)),SUMIFS({Gyro Retainer Actual Hours Range 2},{Gyro Retainer Actual Hours Range 3},"September 2021",{Gyro Retainer Actual Hours Range 1}, CONTAINS(Resource@row, @cell)))

Because they are pulling from two separate sheets I believe you need to have them in their own SUMIFS function and then SUM them which is what is formula does, by using the SUMIFS instead of SUMIF you can just use multiple conditions without the AND/OR functions.

Kimberly Loveless

I'm going to be honest I am not positive this one is going to work but I am going to try....

=SUM(SUMIFS({Merkle - Retainer Hours Log Range 2},{Merkle - Retainer Hours Log Range 3} ,"September 2021",{Merkle - Retainer Hours Log Range 1}, CONTAINS(Resource@row, @cell)),SUMIFS({Gyro Retainer Actual Hours Range 2},{Gyro Retainer Actual Hours Range 3},"September 2021",{Gyro Retainer Actual Hours Range 1}, CONTAINS(Resource@row, @cell)))

Because they are pulling from two separate sheets I believe you need to have them in their own SUMIFS function and then SUM them which is what is formula does, by using the SUMIFS instead of SUMIF you can just use multiple conditions without the AND/OR functions.

Sarah Bystrom

It worked! Thank you so much :)