sumifs with multiple criteria down down list

February 13, 2020 at 19:00:23
Specs: Windows 7
I have a question about multiple drop down list sum if functional criteria and based on that I want to collect number of hours. For example, in E5 I have a data validation list selection where I have selected "SHIFT" and in next 2 columns I have start and end time to sum number of hours an employee work. In next row E6 I have same data validationlist selection where I have selected break 1hour or break 1/2 hour and in next 2 columns same start and end time to sum break time. Now in a report I want calculate total hours minus break time whether is 1/2 hour or full hour.

See More: sumifs with multiple criteria down down list

Reply ↓  Report •

#1
February 14, 2020 at 11:04:56
If the "Break" drop down choice is either "Break 1 hour" or "Break 1 /2 hour" why not use an IF and subtract either 60 minutes or 30 minutes from the result of the Shift time calculation?

e.g. IF(E5="Break 1 hour", (G5-F5)-1/24,(G5-F5)-.5/24)

IOW, if the Break will always be 1 hour or .1/2 hour, then (at least for this calculation) we don't need to know the actual start and end time of the break.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
February 18, 2020 at 06:03:13
Well, in some cases its neither 1 hour nor 1/2 hour because of short length of the shift. Sometimes it also depends on the manager on duty to shorten the length of the break so it has to be from the drop-down.

Reply ↓  Report •

#3
February 18, 2020 at 09:05:47
Now I'm totally confused. Please keep in mind that we can't see your workbook from where we're sitting, so we can only go by what you say in your words - unless you supply an example of how your sheet actually looks.

In your OP you said: "... in E5 I have a data validation list selection where I have selected "SHIFT" and in next 2 columns I have start and end time to sum number of hours an employee work."

You also said: "In next row E6 I have same data validation list selection where I have selected break 1hour or break 1/2 hour and in next 2 columns same start and end time to sum break time."

How can you have the "same start and end time" in Row 6 as in Row 5 if one is for "work hours" and the other is related "break time"?

In addition, in your latest response you said: "...so it has to be from the drop-down"

What is the "it" that you are referring to? If the drop down contains text choices, what does it have to do with the actual calculations?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
February 18, 2020 at 09:49:37
ok let me write it in more details to give you better understanding here.

E5 is a drop down list where my options are (Sift, Half Hour Break, 1 Hour Break, Meeting, Offsite etc.) in F5 I have start time which is again a drop down (I am trying to avoid formatting error so giving time 7:00 AM, 7:30 AM, 8:00 AM etc) in G5 I have end time (again time from dropdown to choose the ending shift). Now the user has selected Shift in E5 and 9:00 AM in F5 and 5:00 PM in G5 that is calculating 8 hours shift in the end. Now, here comes the interesting part. In next row in E6, from the same drop down user could select 1 hour break or 1/2 hour break, and in F6 and G6 again I have time selection (depend on type of break if its 1 hour break or 1/2 hour break). Now in reporting cell, I want to calculate hours excluding break. If in cell F5 time is 9:00 am and G5 time is 5:00 pm and for break in F6 time is 1:00 pm and G6 2:00 pm (mean 1 hour break). it should calculate 7 hours in the end or if its half hour it should do the math for half hour depend on drop down selection.


Reply ↓  Report •

#5
February 18, 2020 at 11:39:18
I'd like some more clarification, if you don't mind.

1 - re: "...and in F6 and G6 again I have time selection (depend on type of break if its 1 hour break or 1/2 hour break)"

That leads me to assume (which is dangerous) that the drop downs in F6 and G6 are Dependent Drop Downs and that the time selections (increments) change based on the selection in E6. I make that assumption because you said "depend on type of break ..."

Is that correct?

2 - re: "In next row in E6, from the same drop down user could select 1 hour break or 1/2 hour break,"

"the same drop down" implies that Shift, Meeting and Offsite are also available as choices. What happens if a user chooses e.g. Shift again? Is that a mistake or a viable choice? Are the time drop downs dependent on those choices too? If the user chooses Shift in both rows, should the times now add up instead instead of subtracting?

Are protections in place that prevent the user from choosing anything other than one of the 2 Break options?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
February 18, 2020 at 12:52:18
Sure, no problem

1. Its not a dependent dropdown list. It is just one source open list and doesnt have time selection increments.

2. Yes, in that list meeting, offsite and others are also available but if the user select 1 hour break or 1/2 hour break only then it should subtract from the shift. (Note: Shift selection is mandatory at the top of list. Below I am trying to sketch it hope you will better understand.


Activity                 Day 1                    Day 2

Shift               9:00 AM | 5:00 PM        9:00 AM | 1:00 PM  
1 Hr Break          1:00 PM | 2:00 PM
1/2 Hr Break                                11:00 PM | 11:30 PM



In the final report it should say:

Shift Hours           Day 1           Day 2
EMPLOYEE 1           7 Hours         3.5 Hours  


Hope you get the point.

Thanks for your help.


Reply ↓  Report •

#7
February 18, 2020 at 14:55:05
re: 1. Its not a dependent dropdown list. It is just one source open list and doesnt have time selection increments.

Really? What do you call these?

7:00 AM, 7:30 AM, 8:00 AM

re: 2. Yes, in that list meeting, offsite and others are also available but if the user select 1 hour break or 1/2 hour break only then it should subtract from the shift.

So what happens if the user chooses Shift in both E5 and E6? These are users after all and users do dumb things. Any formula that is designed to "subtract" only when a user selects one of the Break options has to be told what to do when any other choice is made.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
February 19, 2020 at 05:13:18
I've got a solution, thank you for your help.

Reply ↓  Report •

#9
February 19, 2020 at 06:38:35
If you wouldn't mind sharing your solution, it would help to close this thread out.

The solution will be stored in the archives for others to access should they have a similar question.

Thanks!


message edited by DerbyDad03


Reply ↓  Report •

Ask Question