Subtracting a max from a min if statement

[robc79]

Hi, i'm new here so please accept my apologies if this is posted in the wrong place.

can anybody please help me with this formula...
i've been trying to get this to work all week with no luck. i have come up with an idea that i think will now work though but i just dont know how to write it into my existing formula criteria.

i am currently using an sumifs formula that looks like this.
=SUMIFS('Picking Targets'!$B$3:$B$5000,'Picking Targets'!$A$3:$A$5000,"")+SUMIFS('Picking Targets'!$B$3:$B$5000,'Picking Targets'!$A$3:$A$5000,$A2,'Picking Targets'!$C$3:$C$5000,B$1)

this formula will add up all the time transactions ('Picking Targets'!$B$3:$B$5000) if the selected hour is (B$1) and the name of the employee matches ($A2) from range ('Picking Targets'!$A$3:$A$5000) but this doesnt work like i wanted it too.
so my next idea is to look up the same criteria but instead of summing up all the times, i just want it to subtract the max. time from the min. time which will give me the the time worked of that hour. i have tried this manually and it works, i just dont know how to put it into this formula with the criteria's

Please help...

[shukla.ankur281190]

Hi Welcome to Forum,

Please upload a sample workbook with expected result.

Go to advance click on attachment link and attach the workbook.

[robc79]

Hi thanks for reply -
i'm trying to work out the activity of an employee during a specific hour, so if they have only worked for 40 mins i can target only the 40 mins instead of an hour.

i have uploaded the sheet as requested, its all very new to me this excel so the file is huge

https://www.dropbox.com/s/jljqb7s9ne...iins.xlsx?dl=0

oh its the hour cells on the last sheet that im having issues with, i basically want it to subtract the Max from the min instead up adding them all together.

[shukla.ankur281190]

Can you please mention some expected result as I'm unable exactly what you want?

[robc79]

basically if you do a simple =b6 (max value) - B3 (min Value) on the picking targets sheet it returns 0:11:05 which is what i want it to return on the scanner sheet in cell b2

the current formula thats in B2 of the last tab

sums up all the transactions in range (Picking Targets'!$B$3:$B$5000)
when it matches the name in (A2) from range (Picking Targets'!$A$3:$A$5000)
and matches the hour (B1) from range ('Picking Targets'!$C$3:$C$5000)

does this make any more sense ??

[shukla.ankur281190]

Try

B2=MAX(IF('Picking Targets'!$A$3:$A$5000&'Picking Targets'!$C$3:$C$5000='Scanner Activity in Time'!$A2&'Scanner Activity in Time'!B$1,'Picking Targets'!$B$3:$B$5000))-MIN(IF('Picking Targets'!$A$3:$A$5000&'Picking Targets'!$C$3:$C$5000='Scanner Activity in Time'!$A2&'Scanner Activity in Time'!B$1,IF('Picking Targets'!$B$3:$B$5000>0,'Picking Targets'!$B$3:$B$5000))) with CRTL+SHIFT+ENTER

[robc79]

you absolute star !! that works great

[shukla.ankur281190]

Note

Name Chris Newton 6 hour Max value is 6:59:37 and min value is 6:07:30 and answer will be in your Scanner Activity in Time sheet 52:07.

Hope it is what you looking for

[shukla.ankur281190]

Thanks for feedback and glad to help you

Please add reputation by clicking * star icon of left corner on your screen and mark tread as solved ( for doing these things see the signature below).