SUMIFS with IF and AND functionality

[ConnerR]

Team, the code and formulas i have typed into a few of my cells trying to figure this out has turned into a monster i have lost control of. Details are below.

This spreadsheet is used to summarize overtime for everyone on a shift for a single day.

Column F gets numbers manually entered. Column G lets the user select over time justification (text) from a drop down list of 16 items. This is fed into a summary table with the formula below ("Equipment" will the justification selected in the list):



=SUMIFS(F2:F58,G2:G58,"Equipment")



The user puts 2 in cell F2 and selects "Equipment" in cell G2. That portion works as expected. This is where i need to add a bit more functionality that i cant figure out. In certain cases an employee will use 2 hours of over time to do two different things. Maybe .5 hours of OT are spent on "House Keeping" and 1.5 hours are spent on "Equipment". Column H has a number manually entered and Column I has the same list from Column G. The user would put 2 in cell F2, select "Equipment" for G2, put .5 for H2, and select "House Keeping" for I2. The summary table needs to be able to put 1.5 into M24 (sum of OT for "Equipment") and .5 into M25 (sum of OT for "House Keeping").

My failed attempt to do this is below:



=SUMIFS(F2:F76,G2:G76,"Equipment")-SUMIFS(H2:H76,I2:I76,"<>Equipment")+SUMIFS(H2:H76,I2:I76,"Equipment")



When this is executed hours from M24 (the "Equipment" sum on the summary table) will be deducted from equipment based on the value of H2 even if the original input to cell G2 was not Equipment.



Thank you in advanced for any help

 

[JimM]

Think this might be what you need

=SUMIFS(F2:F76,G2:G76,"Equipment")-SUMIFS(H2:H76,I2:I76,"<>Equipment",G2:G76,"Equipment")+SUMIFS(H2:H76,I2:I76,"Equipment")

 

[ConnerR]

AHH That did it! Thank you so much! im so bummed that i was that close to the right answer! any chance you could explain why yours works and mine didn't?

 

[JimM]

Basically you were taking away anything in col H no matter if the original justification was Equipment or not, all my formula does is checks that the justification was equipment

You may be better off putting the list of justifications in col L eg L24 = Equipment and using the formula

=SUMIFS(F2:F76,G2:G76,L24)-SUMIFS(H2:H76,I2:I76,"<>"&L24,G2:G76,L24)+SUMIFS(H2:H76,I2:I76,L24)

That way you don't have to change each formula for each justification, just change the word in col L

Hope that makes sense

 

[ConnerR]

Basically you were taking away anything in col H no matter if the original justification was Equipment or not, all my formula does is checks that the justification was equipment

You may be better off putting the list of justifications in col L eg L24 = Equipment and using the formula

=SUMIFS(F2:F76,G2:G76,L24)-SUMIFS(H2:H76,I2:I76,"<>"&L24,G2:G76,L24)+SUMIFS(H2:H76,I2:I76,L24)

That way you don't have to change each formula for each justification, just change the word in col L

Hope that makes sense

It does make sense. I did just that. Thanks for all the help!