Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and Dates
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and Dates
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and Dates
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and Dates
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and Dates
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and Dates
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) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and Dates
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of data between two dates - tried SUMIF and it returned "0" | Excel Worksheet Functions | |||
SUMIF for Dates / Months ? | Excel Worksheet Functions | |||
SUMIF function - criteria of between two dates. How? | Excel Worksheet Functions | |||
SUMIF USING DATES | Excel Discussion (Misc queries) | |||
SUMIF between dates | Excel Worksheet Functions |