| Home |
| Search |
| Today's Posts |
|
#7
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 |
| 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 | |||
Threaded Mode
