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

Try:

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

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


with @tiscali.co.uk


wrote in message
ups.com...
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

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

A very small variation

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

--
---
HTH

Bob

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



wrote in message
ups.com...
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

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!D2:D120)

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


with @tiscali.co.uk


wrote in message
ups.com...
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

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

Thanks for your help!

-Karl

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


with @tiscali.co.uk


wrote in message
oups.com...
Awesome! That is exacltly what I needed!!!!

Thanks for your help!

-Karl

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

" wrote:

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