| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum vales found with a date range.
ok I cand understand the answers to other similar topics posted here so i am
going to have to try to explain my self... I have two .xls sheet (WorkBook1 & Woorkbook2) In Woorkbook1 on Sheet1 I have in column A incrementing dates... In Woorkbook1 on Sheet1 I have in column B Total Run Time... In Woorkbook2 on Sheet2 I have in cell A50 the START date... In Woorkbook2 on Sheet2 I have in cell A51 the END date... I am looking for a furmula that will sum the run time between and including the START and END dates. I have succeded in having the formula return a value for a single date but not for the sum of the values found winthin a date range... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum vales found with a date range.
try something like
=Sumif('Woorkbook1.xls]Sheet1'!A:A,"<='[Woorkbook2.xls]Sheet2'!A51,'Woorkbook1.xls]Sheet1'!C:C)-Sumif('Woorkbook1.xls]Sheet1'!A:A,"'[Woorkbook2.xls]Sheet2'!A50,'Woorkbook1.xls]Sheet1'!C:C) "Will" wrote: ok I cand understand the answers to other similar topics posted here so i am going to have to try to explain my self... I have two .xls sheet (WorkBook1 & Woorkbook2) In Woorkbook1 on Sheet1 I have in column A incrementing dates... In Woorkbook1 on Sheet1 I have in column B Total Run Time... In Woorkbook2 on Sheet2 I have in cell A50 the START date... In Woorkbook2 on Sheet2 I have in cell A51 the END date... I am looking for a furmula that will sum the run time between and including the START and END dates. I have succeded in having the formula return a value for a single date but not for the sum of the values found winthin a date range... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum vales found with a date range.
Try this:
=SUMPRODUCT(--([WorkBook1.xls]Sheet1!$A$1:$A$10=$A$50),--([WorkBook1.xls]Sheet1!$A$1:$A$10<=$A$51),([WorkBook1.xls]Sheet1!$B$1:$B$10)) HTH, Elkar "Will" wrote: ok I cand understand the answers to other similar topics posted here so i am going to have to try to explain my self... I have two .xls sheet (WorkBook1 & Woorkbook2) In Woorkbook1 on Sheet1 I have in column A incrementing dates... In Woorkbook1 on Sheet1 I have in column B Total Run Time... In Woorkbook2 on Sheet2 I have in cell A50 the START date... In Woorkbook2 on Sheet2 I have in cell A51 the END date... I am looking for a furmula that will sum the run time between and including the START and END dates. I have succeded in having the formula return a value for a single date but not for the sum of the values found winthin a date range... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum vales found with a date range.
Ignorre my response, I totally goofed it up
"bj" wrote: try something like =Sumif('Woorkbook1.xls]Sheet1'!A:A,"<='[Woorkbook2.xls]Sheet2'!A51,'Woorkbook1.xls]Sheet1'!C:C)-Sumif('Woorkbook1.xls]Sheet1'!A:A,"'[Woorkbook2.xls]Sheet2'!A50,'Woorkbook1.xls]Sheet1'!C:C) "Will" wrote: ok I cand understand the answers to other similar topics posted here so i am going to have to try to explain my self... I have two .xls sheet (WorkBook1 & Woorkbook2) In Woorkbook1 on Sheet1 I have in column A incrementing dates... In Woorkbook1 on Sheet1 I have in column B Total Run Time... In Woorkbook2 on Sheet2 I have in cell A50 the START date... In Woorkbook2 on Sheet2 I have in cell A51 the END date... I am looking for a furmula that will sum the run time between and including the START and END dates. I have succeded in having the formula return a value for a single date but not for the sum of the values found winthin a date range... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum vales found with a date range.
SUMIF won't work on closed files. Better to use SUMPRODUCT.
Biff "bj" wrote in message ... try something like =Sumif('Woorkbook1.xls]Sheet1'!A:A,"<='[Woorkbook2.xls]Sheet2'!A51,'Woorkbook1.xls]Sheet1'!C:C)-Sumif('Woorkbook1.xls]Sheet1'!A:A,"'[Woorkbook2.xls]Sheet2'!A50,'Woorkbook1.xls]Sheet1'!C:C) "Will" wrote: ok I cand understand the answers to other similar topics posted here so i am going to have to try to explain my self... I have two .xls sheet (WorkBook1 & Woorkbook2) In Woorkbook1 on Sheet1 I have in column A incrementing dates... In Woorkbook1 on Sheet1 I have in column B Total Run Time... In Woorkbook2 on Sheet2 I have in cell A50 the START date... In Woorkbook2 on Sheet2 I have in cell A51 the END date... I am looking for a furmula that will sum the run time between and including the START and END dates. I have succeded in having the formula return a value for a single date but not for the sum of the values found winthin a date range... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum vales found with a date range.
try in sheet 1 woorkbook1
=sumif(A:A,"<=Indirect("'[Woorkbook2.xls]Sheet2'!A51")",B:B)-sumif(A:A,"Indirect("'[Woorkbook2.xls]Sheet2'!A50")",B:B) "bj" wrote: Ignorre my response, I totally goofed it up "bj" wrote: try something like =Sumif('Woorkbook1.xls]Sheet1'!A:A,"<='[Woorkbook2.xls]Sheet2'!A51,'Woorkbook1.xls]Sheet1'!C:C)-Sumif('Woorkbook1.xls]Sheet1'!A:A,"'[Woorkbook2.xls]Sheet2'!A50,'Woorkbook1.xls]Sheet1'!C:C) "Will" wrote: ok I cand understand the answers to other similar topics posted here so i am going to have to try to explain my self... I have two .xls sheet (WorkBook1 & Woorkbook2) In Woorkbook1 on Sheet1 I have in column A incrementing dates... In Woorkbook1 on Sheet1 I have in column B Total Run Time... In Woorkbook2 on Sheet2 I have in cell A50 the START date... In Woorkbook2 on Sheet2 I have in cell A51 the END date... I am looking for a furmula that will sum the run time between and including the START and END dates. I have succeded in having the formula return a value for a single date but not for the sum of the values found winthin a date range... |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum vales found with a date range.
OK Great that works... Now how can I ask a similar question but this time I
want to return the sum for the Month of March. I assume I can so something like this: =SUMPRODUCT(--([WorkBook1.xls]Sheet1!$A$1:$A$10<= How do I place the MONTH HERE??),([WorkBook1.xls]Sheet1!$B$1:$B$10)) I have a cell with the month typed using text "March" do I need to change this to Date format and enter 03/2007? "Elkar" wrote: Try this: =SUMPRODUCT(--([WorkBook1.xls]Sheet1!$A$1:$A$10=$A$50),--([WorkBook1.xls]Sheet1!$A$1:$A$10<=$A$51),([WorkBook1.xls]Sheet1!$B$1:$B$10)) HTH, Elkar "Will" wrote: ok I cand understand the answers to other similar topics posted here so i am going to have to try to explain my self... I have two .xls sheet (WorkBook1 & Woorkbook2) In Woorkbook1 on Sheet1 I have in column A incrementing dates... In Woorkbook1 on Sheet1 I have in column B Total Run Time... In Woorkbook2 on Sheet2 I have in cell A50 the START date... In Woorkbook2 on Sheet2 I have in cell A51 the END date... I am looking for a furmula that will sum the run time between and including the START and END dates. I have succeded in having the formula return a value for a single date but not for the sum of the values found winthin a date range... |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum vales found with a date range.
Say you enter your "Text" month in C1.
Try this: =SUMPRODUCT((TEXT(A1:A10,"mmmm")=C1)*B1:B10) You can add your path to the formula. FWIW, You can use the 3 letter abbreviation for the months, so that you don't have to type the full name, like "September". If you use Jan, Oct, Sep, ... etc., revise the formula to look for only 3 characters: =SUMPRODUCT((TEXT(A1:A10,"mmm")=C1)*B1:B10) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Will" wrote in message ... OK Great that works... Now how can I ask a similar question but this time I want to return the sum for the Month of March. I assume I can so something like this: =SUMPRODUCT(--([WorkBook1.xls]Sheet1!$A$1:$A$10<= How do I place the MONTH HERE??),([WorkBook1.xls]Sheet1!$B$1:$B$10)) I have a cell with the month typed using text "March" do I need to change this to Date format and enter 03/2007? "Elkar" wrote: Try this: =SUMPRODUCT(--([WorkBook1.xls]Sheet1!$A$1:$A$10=$A$50),--([WorkBook1.xls]Sheet1!$A$1:$A$10<=$A$51),([WorkBook1.xls]Sheet1!$B$1:$B$10)) HTH, Elkar "Will" wrote: ok I cand understand the answers to other similar topics posted here so i am going to have to try to explain my self... I have two .xls sheet (WorkBook1 & Woorkbook2) In Woorkbook1 on Sheet1 I have in column A incrementing dates... In Woorkbook1 on Sheet1 I have in column B Total Run Time... In Woorkbook2 on Sheet2 I have in cell A50 the START date... In Woorkbook2 on Sheet2 I have in cell A51 the END date... I am looking for a furmula that will sum the run time between and including the START and END dates. I have succeded in having the formula return a value for a single date but not for the sum of the values found winthin a date range... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extract matching vales | Excel Discussion (Misc queries) | |||
| Conditional format if cell match found in another range of cells | Excel Worksheet Functions | |||
| Conditional formatting if value in cell is found in a named range | Excel Worksheet Functions | |||
| Need an OR Function that will allow for Text vales......... | Excel Worksheet Functions | |||
| Validation error: Says range not found and it is there? | Excel Discussion (Misc queries) | |||
Linear Mode
