Sum Cells when Values Change in Two Other Columns

[rnbblsmm]

So I have an employee mileage log and I would like my spreadsheet to be able to generate subtotals based on shift (shifts are denoted by a change in date and/or participant). I have come at this project from a number of different angles but keep hitting a wall. I did find a great formula in an article I found, but there are other factors that need to be incorporated into the formula and I'm not sure how to do that.


=IF(A9<>A8,SUM($H$8:H8)-SUM($M$7:M7),"")


Essentially, I need a formula that will sum all cells in my desired range that 1) are on the same day, 2) are associated with the same participant, and 3) are not part of the employee's commute.

Ideally, I'd like these subtotals to generate as employees add data to the spreadsheet, rather than someone needing to use the built-in filter feature or built-in subtotal feature after the fact. Anyone have any ideas?

A = Date
B= Participant (generated from a drop-down)
G= Includes commute? (generated from a drop-down Y/N)
H= Miles Traveled (the values I want to sum)

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[rnbblsmm]

Thank you for the suggestions.

I have attached a spreadsheet with some dummy data. Essentially, I'm hoping to find a formula that will calculate subtotals for each "shift." Then, I would like all shift totals greater than 20 miles to be subtracted from the mileage total(L4)only when the overages have not been approved (column P). If there were a way to generate totals in column M any time the date and participant changed in columns A and C (excluding commutes), that would be ideal. I was able to generate shift totals based on that criteria in the Shift Totals tab, but I haven't been able to find a way to extract the pertinent data and arrange it in the way I need to on the Mileage tab so I'm hoping there's a formula I can use in column M that will streamline the process. Any suggestions?

[rnbblsmm]

This is what I imagine it would like like but my logic or syntax must be off.

=IF((AND(A9<>A8,C9<>C8,A8="N")),SUM($H$8:H8)-SUM($M$7:M7),"")

[Pete_UK]

You can use this in M8:

=IF(OR(A9<>A8,C9<>C8),SUMIFS($H$8:H8,$A$8:A8,A8,$C$8:C8,C8)-COUNTIFS($A$8:A8,A8,$C$8:C8,C8,$G$8:G8,"Y")*$L$3,"")

then copy down. I've taken the view that as you have a commute distance in L3, then if the distance is 27 and this includes the commute, then 7 miles should be added on to the figures that you show, so the numbers differ from yours. This is easy to change if you only want to add the mileage where Include Commute is N.

Hope this helps.

Pete

[Pete_UK]

This is the formula to use in M8 if you want the numbers to be the same as yours:

=IF(OR(A9<>A8,C9<>C8),SUMIFS($H$8:H8,$A$8:A8,A8,$C$8:C8,C8,$G$8:G8,"N"),"")

Copy down as required.

Hope this helps.

Pete

[rnbblsmm]

Thank you, Pete! That does the trick! I appreciate your help!

[Pete_UK]

You're welcome.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete