SUMIF and Dates

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

How about

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

--

HTH

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


"Mike" <Mike@discussions.microsoft.com> wrote in message
news:372B5D23-1014-4484-A555-6D058AE2DD3C@microsoft.com...
> 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
>
>

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


Mike wrote:
> 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
>
>

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)

--

HTH

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


"Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message
news:439F3827.6050500@nospamtechnology4u.co.uk...
> 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=$A
2),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
>
>
> Mike wrote:
> > 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
> >
> >

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


Bob Phillips wrote:
> 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)
>

Thanks so much.

"Roger Govier" wrote:

> 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
>
>
> Bob Phillips wrote:
> > 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)
> >
>

Thanks so much.

"Bob Phillips" wrote:

> 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)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Roger Govier" <roger@nospamtechnology4u.co.uk> wrote in message
> news:439F3827.6050500@nospamtechnology4u.co.uk...
> > 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=$A
> 2),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
> >
> >
> > Mike wrote:
> > > 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
> > >
> > >
>
>
>

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


"Mike" wrote:

> 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
>
>