SUMIFS with IF and AND functionality

ConnerR

New Member
Joined
Jan 23, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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").



clipboard_image_0.png




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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
686
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

New Member
Joined
Jan 23, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 11, 2003
Messages
686
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

New Member
Joined
Jan 23, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,388
Messages
5,601,382
Members
414,448
Latest member
Jessica 22664

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top