SUMIF and Dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 13 Dec 2005 19:59 GMT

I have a worksheet that looks as follows:
(SHEET 1)

A B C
Location    Start Date    Monthly Estimate
Office 1    1/1/06    1,400
Office 2    1/1/06    15,800
Office 3    1/1/06    2,840
Office 4    1/1/06    2,080
Office 5    1/1/06    1,460
Repairs    1/1/06    400
TBA    5/1/06    5,000
TBA        0
TBA        0
TBA        0
TBA        0



I will have another worksheet that will look as follows:
(SHEET 2)

A B C D
E F
        January-06    February-06 March-06 April-06    May-06
Facility        23,980    23,980    23,980    23,980    28,980

I am trying to write a formula in Cells B2 and thereafter of Sheet 2, that
will look at the start dates listed in Sheet 1 in Column B and then sum the
monthly rents in Column C if the start date for the item of expense is equal
to or less than the date recorded in Row 2 Sheet 2.

Thanks

Reply to this Message

Bob Phillips - 13 Dec 2005 20:55 GMT

How about

=SUMPRODUCT(--(Sheet1!B2:B200>=--"2006-01-01"),--(Sheet1!B2:B200<--"2006-02-
01"),Sheet1!C2:C200)

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> I have a worksheet that looks as follows:
> (SHEET 1)
[quoted text clipped - 27 lines]
>
> Thanks

Reply to this Message

Roger Govier - 13 Dec 2005 21:07 GMT

Hi Mike

One way, enter in Sheet2 B2
=SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)<=MONTH(B$1),Sheet1$C$2:$C$100)
Copy across through C2:F2

I'm not sure whether you are also trying to add Location in as well.
If so, and if Location is in column A of Sheet2, then amend formula to

=SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)<=MONTH(B$1),--(Sheet1$A$2:$A$100=$A2),Sheet1$C$2:$C$100)

Then copy the whole row of formulae down as far as you wish.

Change ranges to suit.

Regards

Roger Govier

> I have a worksheet that looks as follows:
> (SHEET 1)
[quoted text clipped - 30 lines]
> Thanks
>

Reply to this Message

Bob Phillips - 13 Dec 2005 22:50 GMT

Hi Roger,

A few typos in there, but more interestingly, it doesn't account the year,
and blanks would count as January as well. You could do it in one test as I
did the other day like this

=SUMPRODUCT(--(Sheet1!$B$2:$B$10-DAY(Sheet1!$B$2:$B$10)+1=B$1-DAY(B$1)+1),--
(Sheet1!$A$2:$A$10=$A2),Sheet1!$C$2:$C$10)

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> Hi Mike
>
[quoted text clipped - 4 lines]
> I'm not sure whether you are also trying to add Location in as well.
> If so, and if Location is in column A of Sheet2, then amend formula to

=SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)<=MONTH(B$1),--(Sheet1$A$2:$A$100=$A
2),Sheet1$C$2:$C$100)

> Then copy the whole row of formulae down as far as you wish.
>
[quoted text clipped - 35 lines]
> >
> > Thanks

Reply to this Message

Roger Govier - 14 Dec 2005 00:49 GMT

Hi Bob

Many thanks for picking up on my typos. Dashed off in much haste before
going out. It should have read

=SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)<=MONTH(B$1)),Sheet1!$C$2:$C$100)

Whilst I agree it doesn't account for year, the blank dates have blank
values as well, so would make no difference to the sum.

Judging by the OP's expected result, he wanted cumulative data not
individual months data, and it did not seem to be split by Office, hence the
addition on the extra test as per my second formula doesn't seem to be
required.
(That formula also had the typos and should have read as follows
=SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)<=MONTH(B$1)),(Sheet1!$A$2:$A$100=$A2),Sheet1!$C$2:$C$100)

I think to meet the OP's requirement, the addition of a less than in your
formula, and the omission of the test for column A will give the desired
result (as posted), and would allow for different years.

=SUMPRODUCT(--(Sheet1!$B$2:$B$10-DAY(Sheet1!$B$2:$B$10)+1<=B$1-DAY(B$1)+1),Sheet1!$C$2:$C$10)

Regards

Roger Govier

> Hi Roger,
>
[quoted text clipped - 4 lines]
> =SUMPRODUCT(--(Sheet1!$B$2:$B$10-DAY(Sheet1!$B$2:$B$10)+1=B$1-DAY(B$1)+1),--
> (Sheet1!$A$2:$A$10=$A2),Sheet1!$C$2:$C$10)

Reply to this Message

Mike - 14 Dec 2005 18:14 GMT

Thanks so much.

> Hi Bob
>
[quoted text clipped - 31 lines]
> > =SUMPRODUCT(--(Sheet1!$B$2:$B$10-DAY(Sheet1!$B$2:$B$10)+1=B$1-DAY(B$1)+1),--
> > (Sheet1!$A$2:$A$10=$A2),Sheet1!$C$2:$C$10)

Reply to this Message

Mike - 14 Dec 2005 18:16 GMT

Thanks so much.

> Hi Roger,
>
[quoted text clipped - 59 lines]
> > >
> > > Thanks

Reply to this Message

Scott Wagner - 13 Dec 2005 21:42 GMT

Take a look at these:
http://www.cpearson.com/excel/array.htm
http://www.contextures.com/xlFunctions01.html#SumProduct

> I have a worksheet that looks as follows:
> (SHEET 1)
[quoted text clipped - 30 lines]
> Thanks
>

Reply to this Message