Sumif problem

[karldiffenderfer]

Hello,

I am putting together an excel spreadsheet to manage employee time on
projects. The last function I want to add into this sheet is the
total amount of hours they worked each week over the last 4 weeks. I
have tried to do this with a sumif statement but it appears that sumif
statements cannot have 2 conditions. What I need to do is this...

If the date on "Tracking!" (column E) falls into 22 to 28 days ago
from the first day (Monday) of this week

then add the time from column D (and the same row) and put this total
on sheet "totals!" in cell B9.

Side note - If necessary, I can have a cell that I fill in manually
that will specify the first day for that particular week since week
numbers seem to be pain in excel.

Thanks in advance for your help.

-Karl

 

[Sandy Mann]

Try:

=SUMPRODUCT((Tracking!E1:E120>=(TODAY()-WEEKDAY(TODAY(),2))-27)*(Tracking!E1:E120<=(TODAY()-WEEKDAY(TODAY(),2))-21)*Tracking!D1120)

and Custom format as [hh]:mm for 28 days ago to 22 days ago.

Change the -27 and -21 to -20 and -14 etc. for more recent weeks

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[karldiffenderfer]

Hello Sandy,

I tried exactly what you said but must have mis-understood something.
Here is a link to the excel file. Maybe that will shed some light on
the subject.

http://dev.shireinteractive.com/Project_Time_Karl2.xls

Thanks for your help!

Karl

 

[Bob Phillips]

A very small variation

=SUMPRODUCT((tracking!E2:E120>=(TODAY()-WEEKDAY(TODAY(),2))-27)*
(tracking!E2:E120<=(TODAY()-WEEKDAY(TODAY(),2))-21)*tracking!D2120)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sandy Mann]

Your problem is that you did exactl as I said. <g>

I goofed up on the range for the times by starting it in row 1 instead of
row 2. Use:

=SUMPRODUCT((Tracking!E2:E120>=(TODAY()-WEEKDAY(TODAY(),2))-X)*(Tracking!E2:E120<=(TODAY()-WEEKDAY(TODAY(),2))-Y)*Tracking!D2120)

and replace the X and Y as follows:

4 weeks ago X = 27 Y = 21
3 weeks ago X = 20 Y = 14
2 weeks ago X =13 Y = 7
1 week ago X = 6 Y = 0


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[karldiffenderfer]

Awesome! That is exacltly what I needed!!!!

Thanks for your help!

-Karl

 

[Sandy Mann]

You're welcome but the *fast on the draw* Bob Philips beat me to it <g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[T.Valko]

Try this:

Use a cell to get the current weeks Monday date:

E1 =TODAY()-WEEKDAY(TODAY(),3)

Then:

=SUMPRODUCT(--(A1:A20>=E1-28),--(A1:A20<=E1-22),B1:B20)

Biff