software

I have a workbook with two sheets. Sheet 1 looks as follows:

A B
1 Date
2 MTD Revenue

Sheet 2 will have two columns of data. Column A will contain individual
dates begining with 1/1/2006 in row 1 and ending with 12/31/2006 in Row 365.
In column B will be the daily revenue for each of those days.

In cell B1 on Sheet 1 I want the user to be able to enter a date and in cell
B2 I want a formula that will calculate the month to date revenue up to that
date. In somes cases the formula will be adding just one row and in others it
may be adding up to 31 rows.

In an answer to a previous post of this question I was given the following
formula:

=SUMIF(Sheet2!A:A,quot;lt;=quot;amp;Sheet1!B1,Sheet2!B:B)

This formula doesn't work because it returns the toatl revenue since the
beginning of the year. Let me clarify if I can.

Assume Sheet 2 looks as follows:A B
1 1/1/06 100
2 1/10/06 200
3 1/31/06 300
4 2/2/06 20
5 2/12/06 30
6 2/22/06 55

If cell B1 in Sheet 1 is populated as follows, then these are the results I
would expect to see:

Value of B1 Result
1/1/06 100
1/2/06 100
1/10/06 300
1/17/06 300
1/31/06 600
2/1/06 0
2/15/06 50
2/28/06 105

Any thoughts? I tried the MONTH funtion but I could not get it to work.=SUMPRODUCT(--(MONTH(Sheet2!A1:A366)=MONTH(D1)),Sheet2!B1:B366)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Mikequot; gt; wrote in message
...
gt; I have a workbook with two sheets. Sheet 1 looks as follows:
gt;
gt; A B
gt; 1 Date
gt; 2 MTD Revenue
gt;
gt; Sheet 2 will have two columns of data. Column A will contain individual
gt; dates begining with 1/1/2006 in row 1 and ending with 12/31/2006 in Row
365.
gt; In column B will be the daily revenue for each of those days.
gt;
gt; In cell B1 on Sheet 1 I want the user to be able to enter a date and in
cell
gt; B2 I want a formula that will calculate the month to date revenue up to
that
gt; date. In somes cases the formula will be adding just one row and in others
it
gt; may be adding up to 31 rows.
gt;
gt; In an answer to a previous post of this question I was given the following
gt; formula:
gt;
gt; =SUMIF(Sheet2!A:A,quot;lt;=quot;amp;Sheet1!B1,Sheet2!B:B)
gt;
gt; This formula doesn't work because it returns the toatl revenue since the
gt; beginning of the year. Let me clarify if I can.
gt;
gt; Assume Sheet 2 looks as follows:
gt;
gt;
gt; A B
gt; 1 1/1/06 100
gt; 2 1/10/06 200
gt; 3 1/31/06 300
gt; 4 2/2/06 20
gt; 5 2/12/06 30
gt; 6 2/22/06 55
gt;
gt; If cell B1 in Sheet 1 is populated as follows, then these are the results
I
gt; would expect to see:
gt;
gt; Value of B1 Result
gt; 1/1/06 100
gt; 1/2/06 100
gt; 1/10/06 300
gt; 1/17/06 300
gt; 1/31/06 600
gt; 2/1/06 0
gt; 2/15/06 50
gt; 2/28/06 105
gt;
gt; Any thoughts? I tried the MONTH funtion but I could not get it to work.
gt;
Try something like this:
C1:
=SUMPRODUCT((Sheet1!$A$1:$A$100lt;=B1)*(Sheet1!$A$1: $A$100gt;=(B1-DAY(B1) 1))*Sheet1!$B$1:$B$100)
Copy down as far as needed

Change range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Mikequot; wrote:

gt; I have a workbook with two sheets. Sheet 1 looks as follows:
gt;
gt; A B
gt; 1 Date
gt; 2 MTD Revenue
gt;
gt; Sheet 2 will have two columns of data. Column A will contain individual
gt; dates begining with 1/1/2006 in row 1 and ending with 12/31/2006 in Row 365.
gt; In column B will be the daily revenue for each of those days.
gt;
gt; In cell B1 on Sheet 1 I want the user to be able to enter a date and in cell
gt; B2 I want a formula that will calculate the month to date revenue up to that
gt; date. In somes cases the formula will be adding just one row and in others it
gt; may be adding up to 31 rows.
gt;
gt; In an answer to a previous post of this question I was given the following
gt; formula:
gt;
gt; =SUMIF(Sheet2!A:A,quot;lt;=quot;amp;Sheet1!B1,Sheet2!B:B)
gt;
gt; This formula doesn't work because it returns the toatl revenue since the
gt; beginning of the year. Let me clarify if I can.
gt;
gt; Assume Sheet 2 looks as follows:
gt;
gt;
gt; A B
gt; 1 1/1/06 100
gt; 2 1/10/06 200
gt; 3 1/31/06 300
gt; 4 2/2/06 20
gt; 5 2/12/06 30
gt; 6 2/22/06 55
gt;
gt; If cell B1 in Sheet 1 is populated as follows, then these are the results I
gt; would expect to see:
gt;
gt; Value of B1 Result
gt; 1/1/06 100
gt; 1/2/06 100
gt; 1/10/06 300
gt; 1/17/06 300
gt; 1/31/06 600
gt; 2/1/06 0
gt; 2/15/06 50
gt; 2/28/06 105
gt;
gt; Any thoughts? I tried the MONTH funtion but I could not get it to work.
gt;

You can change the formula you've already got to subtract out anything from
prior months:
=SUMIF(Sheet2!A:A,quot;lt;=quot;amp;Sheet1!B1,Sheet2!B:B) -
SUMIF(Sheet2!A:A,quot;lt;=quot;amp;(Sheet1!B1-DAY(Sheet1!B1)),Sheet2!B:B)
Sheet1!B1 - DAY(Sheet1!B1) will return the last day of the prior month,
subtracting out results with dates lt;= that day will leave only the MTD totals.
Note that this all requires that you have strictly dates (no times) in
Sheet2 column A.
HTH. --Bruce

quot;Mikequot; wrote:

gt; I have a workbook with two sheets. Sheet 1 looks as follows:
gt;
gt; A B
gt; 1 Date
gt; 2 MTD Revenue
gt;
gt; Sheet 2 will have two columns of data. Column A will contain individual
gt; dates begining with 1/1/2006 in row 1 and ending with 12/31/2006 in Row 365.
gt; In column B will be the daily revenue for each of those days.
gt;
gt; In cell B1 on Sheet 1 I want the user to be able to enter a date and in cell
gt; B2 I want a formula that will calculate the month to date revenue up to that
gt; date. In somes cases the formula will be adding just one row and in others it
gt; may be adding up to 31 rows.
gt;
gt; In an answer to a previous post of this question I was given the following
gt; formula:
gt;
gt; =SUMIF(Sheet2!A:A,quot;lt;=quot;amp;Sheet1!B1,Sheet2!B:B)
gt;
gt; This formula doesn't work because it returns the toatl revenue since the
gt; beginning of the year. Let me clarify if I can.
gt;
gt; Assume Sheet 2 looks as follows:
gt;
gt;
gt; A B
gt; 1 1/1/06 100
gt; 2 1/10/06 200
gt; 3 1/31/06 300
gt; 4 2/2/06 20
gt; 5 2/12/06 30
gt; 6 2/22/06 55
gt;
gt; If cell B1 in Sheet 1 is populated as follows, then these are the results I
gt; would expect to see:
gt;
gt; Value of B1 Result
gt; 1/1/06 100
gt; 1/2/06 100
gt; 1/10/06 300
gt; 1/17/06 300
gt; 1/31/06 600
gt; 2/1/06 0
gt; 2/15/06 50
gt; 2/28/06 105
gt;
gt; Any thoughts? I tried the MONTH funtion but I could not get it to work.
gt;

Thanks. This worked.quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt; C1:
gt; =SUMPRODUCT((Sheet1!$A$1:$A$100lt;=B1)*(Sheet1!$A$1: $A$100gt;=(B1-DAY(B1) 1))*Sheet1!$B$1:$B$100)
gt; Copy down as far as needed
gt;
gt; Change range references to suit your situation.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Mikequot; wrote:
gt;
gt; gt; I have a workbook with two sheets. Sheet 1 looks as follows:
gt; gt;
gt; gt; A B
gt; gt; 1 Date
gt; gt; 2 MTD Revenue
gt; gt;
gt; gt; Sheet 2 will have two columns of data. Column A will contain individual
gt; gt; dates begining with 1/1/2006 in row 1 and ending with 12/31/2006 in Row 365.
gt; gt; In column B will be the daily revenue for each of those days.
gt; gt;
gt; gt; In cell B1 on Sheet 1 I want the user to be able to enter a date and in cell
gt; gt; B2 I want a formula that will calculate the month to date revenue up to that
gt; gt; date. In somes cases the formula will be adding just one row and in others it
gt; gt; may be adding up to 31 rows.
gt; gt;
gt; gt; In an answer to a previous post of this question I was given the following
gt; gt; formula:
gt; gt;
gt; gt; =SUMIF(Sheet2!A:A,quot;lt;=quot;amp;Sheet1!B1,Sheet2!B:B)
gt; gt;
gt; gt; This formula doesn't work because it returns the toatl revenue since the
gt; gt; beginning of the year. Let me clarify if I can.
gt; gt;
gt; gt; Assume Sheet 2 looks as follows:
gt; gt;
gt; gt;
gt; gt; A B
gt; gt; 1 1/1/06 100
gt; gt; 2 1/10/06 200
gt; gt; 3 1/31/06 300
gt; gt; 4 2/2/06 20
gt; gt; 5 2/12/06 30
gt; gt; 6 2/22/06 55
gt; gt;
gt; gt; If cell B1 in Sheet 1 is populated as follows, then these are the results I
gt; gt; would expect to see:
gt; gt;
gt; gt; Value of B1 Result
gt; gt; 1/1/06 100
gt; gt; 1/2/06 100
gt; gt; 1/10/06 300
gt; gt; 1/17/06 300
gt; gt; 1/31/06 600
gt; gt; 2/1/06 0
gt; gt; 2/15/06 50
gt; gt; 2/28/06 105
gt; gt;
gt; gt; Any thoughts? I tried the MONTH funtion but I could not get it to work.
gt; gt;