SumIF using AND OR
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,
Best Answer

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.
Answers

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.

It worked! Thank you so much :)
Help Article Resources
Categories
Check out the Formula Handbook template!