Calculate sum based on user criteria in excel 3

[Gilbertjoe]

I have several worksheets that calculate hours worked.
On each worksheet there are the following cells:
Date
Start time
Stop time time
Hours worked
(I already have the hours cells calculating time between start and stop times)
I would like to have 3 cells at the top of each worksheet that the user can enter a start DATE in one cell, a Stop DATE in another cell, and the 3rd cell will sum the total hours between those two dates.
I don't want the user inserting rows or cells and then autosuming the hours so I'm thinking that just 3 cells at the top of the worksheet the user can input two dates on and get total hours would be better. How is this done?

 

[tsurikov]

Hi,

I don't think you can do it with just the three cells you have in mind. My first thought was to use SUMIF, but you can't specify a criterion to examine individual cells (only general ones, like ">30&quot.

How about this: Let's say Columns B and C are your date and hours worked, respectively, starting at row 5. Let's also say your start and end dates are in cells B1 and B2, respectively. Now fill Column D with something like

=If(And(B5>$B$1,B5<=$B$2),C5,0)

and sum Column D to get the answer you want. If you want to keep your spreadsheet clean for your users, you can make Column D very narrow or hide it altogether..!

Hope this helps,
--Michael

 

[xlbo]

If input dates in A1 & B1, worked dates in col A and hours in B then
=SUMIF(A3:A300,&quot;<=&quot; & B1,B3:B300)-SUMIF(A3:A300,&quot;<&quot; & A1,B3:B300)

will give you what you want (NB - this would be INclusive of the 2 dates entered)



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tav1035]

Gilbertjoe,
A B C D
1 Date Start Stop Total
2 8/17/2002 1:21:22 PM 3:44:51 PM 2:23:29
3
4
5 Date Start Stop Total
6 8/16/2002 9:35:55 AM 11:02:36 AM 1:26:41
7 8/16/2002 7:56:32 PM 8:07:43 PM 0:11:11
8 8/17/2002 1:21:22 PM 3:44:51 PM 2:23:29
9 8/17/2002 5:33:53 AM 11:02:36 AM 1:04:01

Add this formula into cell D2
=SUMPRODUCT((A2=A3:A500)*(B2=B3:B500)*(C2=C3:C500),D3500)

The problem with this is Row2 (criteria) has to be type EXACTLY the way it's entered in the data.



Take a look at this thread first->

Pivot
thread68-615092
tav

 

[tsurikov]

Hi xlbo,

I am intrigued by your solution. Can you explain the syntax of your SUMIF criteria? I'm used to simple text-only criteria, like in the Excel help page for SUMIF - never seen anything like &quot;& B1,B3:B300&quot;.

Thanks!
--Michael

 

[xlbo]

it's just concatenating the criteria together. The criteria for a SUMIF must be text

what &quot;<=&quot; & B1 does is to concatentate the &quot;<=&quot; and the VALUE in B1. Beacuse it is concatenated, it automatically becomes text and therefore works in a SUMIF. This technique can also be used for COUNTIFs and any other function that requires a criteria

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tsurikov]

Thanks, xlbo! I've been looking for something like that for ages! Great to know!

Cheers,
--Michael

 

[tav1035]

Gilbertjoe,
After reading xlbo's solution, I realized that I was way off in that I wasn't using (between) Start & Stop dates, but exact times. Here is an attempt to repair the formula....
cudos to xlbo for the concatentating formula.

A B C
1 StartDate StopDate Total
2 8/17/2002 8/17/2002 3:27:30
3
4
5 Date Start Stop Total
6 8/16/2002 9:35:55 AM 11:02:36 AM 1:26:41
7 8/16/2002 7:56:32 PM 8:07:43 PM 0:11:11
8 8/17/2002 1:21:22 PM 3:44:51 PM 2:23:29
9 8/17/2002 5:33:53 AM 11:02:36 AM 1:04:01

Add this formula into cell C2
=SUMPRODUCT((A2<=A3:A500)*(B2>=A3:A500),D3500)