software

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

A B C
LocationStart DateMonthly Estimate
Office 11/1/061,400
Office 21/1/0615,800
Office 31/1/062,840
Office 41/1/062,080
Office 51/1/061,460
Repairs1/1/06400
TBA5/1/065,000
TBA0
TBA0
TBA0
TBA0
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-06May-06
Facility23,98023,98023,98023,98028,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:B200gt;=--quot;2006-01-01quot;),--(Sheet1!B2:B200lt;--quot;2006-02-
01quot;),Sheet1!C2:C200)

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Mikequot; gt; wrote in message
...
gt; I have a worksheet that looks as follows:
gt; (SHEET 1)
gt;
gt; A B C
gt; Location Start Date Monthly Estimate
gt; Office 1 1/1/06 1,400
gt; Office 2 1/1/06 15,800
gt; Office 3 1/1/06 2,840
gt; Office 4 1/1/06 2,080
gt; Office 5 1/1/06 1,460
gt; Repairs 1/1/06 400
gt; TBA 5/1/06 5,000
gt; TBA 0
gt; TBA 0
gt; TBA 0
gt; TBA 0
gt;
gt;
gt;
gt; I will have another worksheet that will look as follows:
gt; (SHEET 2)
gt;
gt; A B C D
gt; E F
gt; January-06 February-06 March-06 April-06 May-06
gt; Facility 23,980 23,980 23,980 23,980 28,980
gt;
gt; I am trying to write a formula in Cells B2 and thereafter of Sheet 2, that
gt; will look at the start dates listed in Sheet 1 in Column B and then sum
the
gt; monthly rents in Column C if the start date for the item of expense is
equal
gt; to or less than the date recorded in Row 2 Sheet 2.
gt;
gt; Thanks
gt;
gt;
Hi Mike

One way, enter in Sheet2 B2
=SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)lt;=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)lt;=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 GovierMike wrote:
gt; I have a worksheet that looks as follows:
gt; (SHEET 1)
gt;
gt; A B C
gt; LocationStart DateMonthly Estimate
gt; Office 11/1/061,400
gt; Office 21/1/0615,800
gt; Office 31/1/062,840
gt; Office 41/1/062,080
gt; Office 51/1/061,460
gt; Repairs1/1/06400
gt; TBA5/1/065,000
gt; TBA0
gt; TBA0
gt; TBA0
gt; TBA0
gt;
gt;
gt;
gt; I will have another worksheet that will look as follows:
gt; (SHEET 2)
gt;
gt; A B C D
gt; E F
gt; January-06 February-06 March-06 April-06May-06
gt; Facility23,98023,98023,98023,98028,980
gt;
gt; I am trying to write a formula in Cells B2 and thereafter of Sheet 2, that
gt; will look at the start dates listed in Sheet 1 in Column B and then sum the
gt; monthly rents in Column C if the start date for the item of expense is equal
gt; to or less than the date recorded in Row 2 Sheet 2.
gt;
gt; Thanks
gt;
gt;

Take a look at these:
www.cpearson.com/excel/array.htm
www.contextures.com/xlFunctio...tml#SumProductquot;Mikequot; wrote:

gt; I have a worksheet that looks as follows:
gt; (SHEET 1)
gt;
gt; A B C
gt; LocationStart DateMonthly Estimate
gt; Office 11/1/061,400
gt; Office 21/1/0615,800
gt; Office 31/1/062,840
gt; Office 41/1/062,080
gt; Office 51/1/061,460
gt; Repairs1/1/06400
gt; TBA5/1/065,000
gt; TBA0
gt; TBA0
gt; TBA0
gt; TBA0
gt;
gt;
gt;
gt; I will have another worksheet that will look as follows:
gt; (SHEET 2)
gt;
gt; A B C D
gt; E F
gt; January-06 February-06 March-06 April-06May-06
gt; Facility23,98023,98023,98023,98028,980
gt;
gt; I am trying to write a formula in Cells B2 and thereafter of Sheet 2, that
gt; will look at the start dates listed in Sheet 1 in Column B and then sum the
gt; monthly rents in Column C if the start date for the item of expense is equal
gt; to or less than the date recorded in Row 2 Sheet 2.
gt;
gt; Thanks
gt;
gt;

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)quot;Roger Govierquot; gt; wrote in message
...
gt; Hi Mike
gt;
gt; One way, enter in Sheet2 B2
gt; =SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)lt;=MONTH(B$1),Sheet1$C$2: $C$100)
gt; Copy across through C2:F2
gt;
gt; I'm not sure whether you are also trying to add Location in as well.
gt; If so, and if Location is in column A of Sheet2, then amend formula to
gt;
gt;
=SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)lt;=MONTH(B$1),--(Sheet1$A$2:$A$100=$A
2),Sheet1$C$2:$C$100)
gt;
gt; Then copy the whole row of formulae down as far as you wish.
gt;
gt; Change ranges to suit.
gt;
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; Mike wrote:
gt; gt; I have a worksheet that looks as follows:
gt; gt; (SHEET 1)
gt; gt;
gt; gt; A B C
gt; gt; Location Start Date Monthly Estimate
gt; gt; Office 1 1/1/06 1,400
gt; gt; Office 2 1/1/06 15,800
gt; gt; Office 3 1/1/06 2,840
gt; gt; Office 4 1/1/06 2,080
gt; gt; Office 5 1/1/06 1,460
gt; gt; Repairs 1/1/06 400
gt; gt; TBA 5/1/06 5,000
gt; gt; TBA 0
gt; gt; TBA 0
gt; gt; TBA 0
gt; gt; TBA 0
gt; gt;
gt; gt;
gt; gt;
gt; gt; I will have another worksheet that will look as follows:
gt; gt; (SHEET 2)
gt; gt;
gt; gt; A B C D
gt; gt; E F
gt; gt; January-06 February-06 March-06 April-06 May-06
gt; gt; Facility 23,980 23,980 23,980 23,980 28,980
gt; gt;
gt; gt; I am trying to write a formula in Cells B2 and thereafter of Sheet 2,
that
gt; gt; will look at the start dates listed in Sheet 1 in Column B and then sum
the
gt; gt; monthly rents in Column C if the start date for the item of expense is
equal
gt; gt; to or less than the date recorded in Row 2 Sheet 2.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt;
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)lt;=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)lt;=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) 1lt;=B$1-DAY(B$1) 1),Sheet1!$C$2:$C$10)Regards

Roger GovierBob Phillips wrote:
gt; Hi Roger,
gt;
gt; A few typos in there, but more interestingly, it doesn't account the year,
gt; and blanks would count as January as well. You could do it in one test as I
gt; did the other day like this
gt;
gt; =SUMPRODUCT(--(Sheet1!$B$2:$B$10-DAY(Sheet1!$B$2:$B$10) 1=B$1-DAY(B$1) 1),--
gt; (Sheet1!$A$2:$A$10=$A2),Sheet1!$C$2:$C$10)
gt;

Thanks so much.

quot;Roger Govierquot; wrote:

gt; Hi Bob
gt;
gt; Many thanks for picking up on my typos. Dashed off in much haste before
gt; going out. It should have read
gt;
gt; =SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)lt;=MONTH(B$1)),Sheet1!$C$ 2:$C$100)
gt;
gt; Whilst I agree it doesn't account for year, the blank dates have blank
gt; values as well, so would make no difference to the sum.
gt;
gt; Judging by the OP's expected result, he wanted cumulative data not
gt; individual months data, and it did not seem to be split by Office, hence the
gt; addition on the extra test as per my second formula doesn't seem to be
gt; required.
gt; (That formula also had the typos and should have read as follows
gt; =SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)lt;=MONTH(B$1)),(Sheet1!$A $2:$A$100=$A2),Sheet1!$C$2:$C$100)
gt;
gt; I think to meet the OP's requirement, the addition of a less than in your
gt; formula, and the omission of the test for column A will give the desired
gt; result (as posted), and would allow for different years.
gt;
gt; =SUMPRODUCT(--(Sheet1!$B$2:$B$10-DAY(Sheet1!$B$2:$B$10) 1lt;=B$1-DAY(B$1) 1),Sheet1!$C$2:$C$10)
gt;
gt;
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; Bob Phillips wrote:
gt; gt; Hi Roger,
gt; gt;
gt; gt; A few typos in there, but more interestingly, it doesn't account the year,
gt; gt; and blanks would count as January as well. You could do it in one test as I
gt; gt; did the other day like this
gt; gt;
gt; gt; =SUMPRODUCT(--(Sheet1!$B$2:$B$10-DAY(Sheet1!$B$2:$B$10) 1=B$1-DAY(B$1) 1),--
gt; gt; (Sheet1!$A$2:$A$10=$A2),Sheet1!$C$2:$C$10)
gt; gt;
gt;

Thanks so much.

quot;Bob Phillipsquot; wrote:

gt; Hi Roger,
gt;
gt; A few typos in there, but more interestingly, it doesn't account the year,
gt; and blanks would count as January as well. You could do it in one test as I
gt; did the other day like this
gt;
gt; =SUMPRODUCT(--(Sheet1!$B$2:$B$10-DAY(Sheet1!$B$2:$B$10) 1=B$1-DAY(B$1) 1),--
gt; (Sheet1!$A$2:$A$10=$A2),Sheet1!$C$2:$C$10)
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt; (remove nothere from the email address if mailing direct)
gt;
gt;
gt; quot;Roger Govierquot; gt; wrote in message
gt; ...
gt; gt; Hi Mike
gt; gt;
gt; gt; One way, enter in Sheet2 B2
gt; gt; =SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)lt;=MONTH(B$1),Sheet1$C$2: $C$100)
gt; gt; Copy across through C2:F2
gt; gt;
gt; gt; I'm not sure whether you are also trying to add Location in as well.
gt; gt; If so, and if Location is in column A of Sheet2, then amend formula to
gt; gt;
gt; gt;
gt; =SUMPRODUCT(--(MONTH(Sheet1!$B$2:$B$100)lt;=MONTH(B$1),--(Sheet1$A$2:$A$100=$A
gt; 2),Sheet1$C$2:$C$100)
gt; gt;
gt; gt; Then copy the whole row of formulae down as far as you wish.
gt; gt;
gt; gt; Change ranges to suit.
gt; gt;
gt; gt; Regards
gt; gt;
gt; gt; Roger Govier
gt; gt;
gt; gt;
gt; gt; Mike wrote:
gt; gt; gt; I have a worksheet that looks as follows:
gt; gt; gt; (SHEET 1)
gt; gt; gt;
gt; gt; gt; A B C
gt; gt; gt; Location Start Date Monthly Estimate
gt; gt; gt; Office 1 1/1/06 1,400
gt; gt; gt; Office 2 1/1/06 15,800
gt; gt; gt; Office 3 1/1/06 2,840
gt; gt; gt; Office 4 1/1/06 2,080
gt; gt; gt; Office 5 1/1/06 1,460
gt; gt; gt; Repairs 1/1/06 400
gt; gt; gt; TBA 5/1/06 5,000
gt; gt; gt; TBA 0
gt; gt; gt; TBA 0
gt; gt; gt; TBA 0
gt; gt; gt; TBA 0
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; I will have another worksheet that will look as follows:
gt; gt; gt; (SHEET 2)
gt; gt; gt;
gt; gt; gt; A B C D
gt; gt; gt; E F
gt; gt; gt; January-06 February-06 March-06 April-06 May-06
gt; gt; gt; Facility 23,980 23,980 23,980 23,980 28,980
gt; gt; gt;
gt; gt; gt; I am trying to write a formula in Cells B2 and thereafter of Sheet 2,
gt; that
gt; gt; gt; will look at the start dates listed in Sheet 1 in Column B and then sum
gt; the
gt; gt; gt; monthly rents in Column C if the start date for the item of expense is
gt; equal
gt; gt; gt; to or less than the date recorded in Row 2 Sheet 2.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;