Sum time spent based on conditions

[YasserKhalil]

Hello everyone
I have two sheets : Sheet1 contains data and in sheet2 I need to calculate the time spent in column H based on conditions

Sheet2:
The Code field in Column A is the first condition
The second condition is the date to be between two dates in range("A1") and range("E1")
In Columns B:G if the cell is not empty then the condition would be the string n range("B4:G4")
---------------------
I will attach the expected results for the first example:
In sheet1 I will put the word "OK" in column L to illustrate the rows that applied all the conditions ...

[YasserKhalil]

Can it be done by formulas or I have to use vba?

[karedog]

Hi,

Although this can be done using formula (SUMIFS() and COUNTIFS()), here is the vba way :

Please Login or Register  to view this content.

Regards

[YasserKhalil]

Thank you very much for this code
I will test it now and I'm sure it will be great of course ..
But if it can be solved by formulas I'm eager to know how ..?
Thanks for help me all the time

[berlan]

Hi YasserKhalil,

formula may not be my forte but did an attempt. It's an array formula that and quite long (confirm by Ctrl+Shift+Enter). Each sum formula is for each sector separately - have tried to make it shorter but failed so far.

In H5:
=SUM(IF(B5>0,LARGE((Sheet1!$C$5:$C$12=$A5)*(Sheet1!$B$5:$B$12>=$A$2)*(Sheet1!$B$5:$B$12<=$E$2)*(Sheet1!$G$5:$G$12=B$4)*(Sheet1!$J$5:$J$12),ROW(INDIRECT("1:"&B5))),0))+SUM(IF(C5>0,LARGE((Sheet1!$C$5:$C$12=$A5)*(Sheet1!$B$5:$B$12>=$A$2)*(Sheet1!$B$5:$B$12<=$E$2)*(Sheet1!$G$5:$G$12=C$4)*(Sheet1!$J$5:$J$12),ROW(INDIRECT("1:"&C5))),0))+SUM(IF(D5>0,LARGE((Sheet1!$C$5:$C$12=$A5)*(Sheet1!$B$5:$B$12>=$A$2)*(Sheet1!$B$5:$B$12<=$E$2)*(Sheet1!$G$5:$G$12=D$4)*(Sheet1!$J$5:$J$12),ROW(INDIRECT("1:"&D5))),0))+SUM(IF(E5>0,LARGE((Sheet1!$C$5:$C$12=$A5)*(Sheet1!$B$5:$B$12>=$A$2)*(Sheet1!$B$5:$B$12<=$E$2)*(Sheet1!$G$5:$G$12=E$4)*(Sheet1!$J$5:$J$12),ROW(INDIRECT("1:"&E5))),0))+SUM(IF(F5>0,LARGE((Sheet1!$C$5:$C$12=$A5)*(Sheet1!$B$5:$B$12>=$A$2)*(Sheet1!$B$5:$B$12<=$E$2)*(Sheet1!$G$5:$G$12=F$4)*(Sheet1!$J$5:$J$12),ROW(INDIRECT("1:"&F5))),0))+SUM(IF(G5>0,LARGE((Sheet1!$C$5:$C$12=$A5)*(Sheet1!$B$5:$B$12>=$A$2)*(Sheet1!$B$5:$B$12<=$E$2)*(Sheet1!$G$5:$G$12=G$4)*(Sheet1!$J$5:$J$12),ROW(INDIRECT("1:"&G5))),0))

where, for example, the highlighted part sums for the sector AR seperately given all the criterias you mentioned.

See attached.

Best,
berlan

[YasserKhalil]

Mr. Karedog
I tested the code .. after trying to run it again I have noticed that it clears the contents of the range B5:G10
See the attachment

[YasserKhalil]

Mr. Berlan
Thank you very much for this formula
But it is too long ..
Is there a way to devise UDF for this mission
I imagine : UDFName(startdate,enddate,code,sectorrange(B4:G4))
Generally thanks a lot for the time spent in this successful formula

[karedog]

You are welcome, and thank you for the reps.

I have amend the code, to make the cells with contents like numeric 1862 is the same as string 1862 :

Please Login or Register  to view this content.

This is my version using Excel formulas :
Formula on B5 :

Please Login or Register  to view this content.

and to be copied to B5:G10



Formula on H5 :

Please Login or Register  to view this content.

and to be copied to H5:H10

Mr. Karedog
I tested the code .. after trying to run it again I have noticed that it clears the contents of the range B5:G10
See the attachment

Yes, the cells must be cleared first, or there will be non zero initial values for arrOut

Is there a way to devise UDF for this mission

Since the results is span across multicell, it means that the UDF is returning array, which is known to be slow.


Regards

[YasserKhalil]

Mr. Karedog
I thought formulas will be difficult .. I tried both formulas and the code and the results were amazing
Thank you very much for your fantastic solutions
Thanks to Mr. Berlan who contributed a great and helpful formula too
Now it is solved

[karedog]

You are welcome Yasser, thanks to you too.


Regards

[YasserKhalil]

Mr. Karedog
I know that I marked the thread as solved ..
But it seems that I will have to use the array formula of Mr. Berlan .. It is the right way to achieve the task ..
The problem is that the required from me now is not to calculate the range("B5:G10") just the required range("H5:H10")

And the range("B4:G4") is fixed in calculation method as Berlan's sloution .. and sometimes the data in that range may be empty
So I was talking about the UDF function in post 7 ?
Sorry for disturbing you again

[karedog]

This is the UDF code :

Please Login or Register  to view this content.

To use this UDF, highlight Sheet2 H5:H10, press F2 and put this formula (and ended with pressing Ctrl Shift Enter --> array UDF) :

Please Login or Register  to view this content.

Regards

[YasserKhalil]

Thank you very much mr. Karedog
I tried clearing the contents of range("C4:G5") so there is only one sector and the value 1 related to it
Pressed Ctrl + Alt + F9 .. the same result 9:15 .. the result should be related to the range ("B4:G4") and if any is empty it is ignored completely from calculations
The result after clearing range("C4:G5") should be 3:00
I hope it is clear now
and sorry again for disturbing you

[karedog]

Yasser, please look at attached file.

From the original data (not changed yet), the criteria is 25/06/2015 <= date <= 30/06/2015,
for Code = 1862, the matched items from sheet1 are :

Row 5     30/06/2015     1862     3:15 
Row 7     30/06/2015     1862     3:00 
Row 8     30/06/2015     1862     2:00 
Row 9     25/06/2015     1862     2:00 
Row 12    28/06/2015     1862     1:00 
                            ------------ 
                        Total    11:15  

and the result from UDF in sheet2 cell H5 is also 11:15, so they are matched.


Now you try to clear the contents of Sheet1 range("C4:G5"), so according to above list, only Row 5 (3:15) is removed, so the total should be 11:15 - 3:15 = 8:00
This also reflected in sheet2 cell H5.

I don't understand what you mean.

[YasserKhalil]

Mr. Karedog
I'm sorry for not clarifying well
In sheet2:
----------
Range("B4:G5") which has the headers of sectors , this range may be cleared .. I need to refresh the results if cleared for example range("C4:G4") and calculate in this case B4 only
So in this case the result should be 3:00
Hope it is clear now

[karedog]

Ok, I get it now, so you want add a new criteria based on Sector field.

This is the UDF :

Please Login or Register  to view this content.

Usage :

Please Login or Register  to view this content.

[YasserKhalil]

I can't believe my eyes
It worked like charm. Really brilliant and awesome
Thank you very much for this great and continuous help

[karedog]

You are welcome, glad I can help.


Regards