User Profile
ConnerR
Copper Contributor
Joined 5 years ago
User Widgets
Recent Discussions
SUMIFS with IF and AND functionality monster
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
