Sum vales found with a date range.

G

Guest

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...
 
G

Guest

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)
 
G

Guest

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
 
G

Guest

Ignorre my response, I totally goofed it up

bj said:
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 said:
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...
Click to expand...
Click to expand...
 
T

T. Valko

SUMIF won't work on closed files. Better to use SUMPRODUCT.

Biff

bj said:
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 said:
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...
Click to expand...
Click to expand...
 
G

Guest

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 said:
Ignorre my response, I totally goofed it up

bj said:
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 said:
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...
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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...
Click to expand...
Click to expand...
 
R

Ragdyer

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 said:
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 said:
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 said:
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...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional sum in a named range 8
Sumproduct using date 3
Conditional Sum date range 2
Sum range of values that meet date criteria 2
User defined menu stops working when workbook saved with a newname??? 4
Using SUMIFS with date range 4
Excel VBA to move cells to sheet2 based on selection in sheet1 0
Sumif function with a date range condition statement 4

Top