| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum values within a specified date range
Please help.
( Sheet 1 ) ( Sheet 2 ) Value Paid Cut-off from Cut-off to Will receive in: SUM 245,94 20.09.2006 21.08.2006 20.09.2006 October ? 243,67 02.11.2006 21.09.2006 20.10.2006 November ? 200,00 27.09.2006 21.10.2006 20.11.2006 December ? I have 2 sheets - one with invoice details and the second sheet should calculate the values of the invoices within a certain period. I need to create a formula that will evaluate invoice paid dates (Paid) and if it is within certain cut-off period (Cut-off from - Cut-off to) it will summ all the values (Value) within this period. I try to use SUMIF function but I can not make it work. Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values within a specified date range
=SUMIF(Sheet1!B:B,"<="&B2,Sheet1!A:A)-SUMIF(Sheet1!B:B,"<"&A2,Sheet1!A:A)
wrote: Please help. ( Sheet 1 ) ( Sheet 2 ) Value Paid Cut-off from Cut-off to Will receive in: SUM 245,94 20.09.2006 21.08.2006 20.09.2006 October ? 243,67 02.11.2006 21.09.2006 20.10.2006 November ? 200,00 27.09.2006 21.10.2006 20.11.2006 December ? I have 2 sheets - one with invoice details and the second sheet should calculate the values of the invoices within a certain period. I need to create a formula that will evaluate invoice paid dates (Paid) and if it is within certain cut-off period (Cut-off from - Cut-off to) it will summ all the values (Value) within this period. I try to use SUMIF function but I can not make it work. Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values within a specified date range
=SUMPRODUCT(--(Sheet1!$B$2:$B$20=Sheet2!A2),--(Sheet1!$B$2:$B$20<=Sheet2!B2),
Sheet1!$A$2:$A$20) -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message oups.com... Please help. ( Sheet 1 ) ( Sheet 2 ) Value Paid Cut-off from Cut-off to Will receive in: SUM 245,94 20.09.2006 21.08.2006 20.09.2006 October ? 243,67 02.11.2006 21.09.2006 20.10.2006 November ? 200,00 27.09.2006 21.10.2006 20.11.2006 December ? I have 2 sheets - one with invoice details and the second sheet should calculate the values of the invoices within a certain period. I need to create a formula that will evaluate invoice paid dates (Paid) and if it is within certain cut-off period (Cut-off from - Cut-off to) it will summ all the values (Value) within this period. I try to use SUMIF function but I can not make it work. Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum values within a specified date range
Thank you!!
Lori wrote: =SUMIF(Sheet1!B:B,"<="&B2,Sheet1!A:A)-SUMIF(Sheet1!B:B,"<"&A2,Sheet1!A:A) wrote: Please help. ( Sheet 1 ) ( Sheet 2 ) Value Paid Cut-off from Cut-off to Will receive in: SUM 245,94 20.09.2006 21.08.2006 20.09.2006 October ? 243,67 02.11.2006 21.09.2006 20.10.2006 November ? 200,00 27.09.2006 21.10.2006 20.11.2006 December ? I have 2 sheets - one with invoice details and the second sheet should calculate the values of the invoices within a certain period. I need to create a formula that will evaluate invoice paid dates (Paid) and if it is within certain cut-off period (Cut-off from - Cut-off to) it will summ all the values (Value) within this period. I try to use SUMIF function but I can not make it work. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fomula for number of days on each month from a date range | Excel Discussion (Misc queries) | |||
| How do I generate a list from a range of values | Excel Worksheet Functions | |||
| How to return a value between date ranges | Excel Worksheet Functions | |||
| HELP - Date Range In 1 Cell Calculation | Excel Worksheet Functions | |||
| Sum range of values that meet date criteria | Excel Discussion (Misc queries) | |||
Linear Mode
