SUMIF and Dates

[Guest]

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

 

[Bob Phillips]

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)

 

[Roger Govier]

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

 

[Guest]

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

 

[Bob Phillips]

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]

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

 

[Guest]

Thanks so much.

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

 

[Guest]

Thanks so much.

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)