ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   calculating between 2 given dates (https://www.excelbanter.com/excel-discussion-misc-queries/181533-calculating-between-2-given-dates.html)

spudsnruf
calculating between 2 given dates
 
Hello

I would like to learn how to calculate amonts in a sumif that are between 2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
......where A8 is currently just a month reference (I mean it looks at cell A8
where I type in the month). So in other words, I'd like it to look up values
in another worksheet called Costs Detail, providing the associated dates are
between, say 1st Feb and end of April. Any help always please.

T. Valko
calculating between 2 given dates
 
One way...

A8 = start date = 2/1/2008
A9 = end date = 4/30/2008

=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs
detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs
detail'!$O$2:$O$518)

Note that in your posted formula you have different sized ranges:

$Q$2:$Q518
$O$2:$O$542



--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Hello

I would like to learn how to calculate amonts in a sumif that are between
2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
.....where A8 is currently just a month reference (I mean it looks at cell
A8
where I type in the month). So in other words, I'd like it to look up
values
in another worksheet called Costs Detail, providing the associated dates
are
between, say 1st Feb and end of April. Any help always please.



Jim Thomlinson
calculating between 2 given dates
 
I assume the equal sign here is an oversight...

)-=SUMIF(
--
HTH...

Jim Thomlinson


"T. Valko" wrote:

One way...

A8 = start date = 2/1/2008
A9 = end date = 4/30/2008

=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs
detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs
detail'!$O$2:$O$518)

Note that in your posted formula you have different sized ranges:

$Q$2:$Q518
$O$2:$O$542



--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Hello

I would like to learn how to calculate amonts in a sumif that are between
2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
.....where A8 is currently just a month reference (I mean it looks at cell
A8
where I type in the month). So in other words, I'd like it to look up
values
in another worksheet called Costs Detail, providing the associated dates
are
between, say 1st Feb and end of April. Any help always please.




T. Valko
calculating between 2 given dates
 
Correction:

=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs
detail'!$O$2:$O$518)-SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs
detail'!$O$2:$O$518)

Removed the equal sign before the 2nd SUMIF.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

A8 = start date = 2/1/2008
A9 = end date = 4/30/2008

=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs
detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs
detail'!$O$2:$O$518)

Note that in your posted formula you have different sized ranges:

$Q$2:$Q518
$O$2:$O$542



--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Hello

I would like to learn how to calculate amonts in a sumif that are between
2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
.....where A8 is currently just a month reference (I mean it looks at
cell A8
where I type in the month). So in other words, I'd like it to look up
values
in another worksheet called Costs Detail, providing the associated dates
are
between, say 1st Feb and end of April. Any help always please.

spudsnruf
calculating between 2 given dates
 
Thanks Very much (and noted the comment about different sized ranges). Is
there actually a way of doing the dates within the formula something like
this..

SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts
detail'!$O$2:$O$542)...ie so that within the formula I list the date before
and after that I want to apply?

Thanks


"T. Valko" wrote:

One way...

A8 = start date = 2/1/2008
A9 = end date = 4/30/2008

=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs
detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs
detail'!$O$2:$O$518)

Note that in your posted formula you have different sized ranges:

$Q$2:$Q518
$O$2:$O$542



--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Hello

I would like to learn how to calculate amonts in a sumif that are between
2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
.....where A8 is currently just a month reference (I mean it looks at cell
A8
where I type in the month). So in other words, I'd like it to look up
values
in another worksheet called Costs Detail, providing the associated dates
are
between, say 1st Feb and end of April. Any help always please.




T. Valko
calculating between 2 given dates
 
calculate amonts in a sumif that are between 2 dates.

In 99 out of 100 cases when someone says "between 2 dates" what they really
mean is greater than or equal to a start date and less than or equal to an
end date.

SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts
detail'!$O$2:$O$542)...

The logic of the above would *exclude* the following dates (m/d/y format):

2/1/2008
4/30/2008


Try one of these:

=SUMIF('Costs detail'!$Q$2:$Q518,"="&DATE(2008,2,1),'Costs
detail'!$O$2:$O$518)-SUMIF('Costs
detail'!$Q$2:$Q518,""&DATE(2008,4,30),'Costs
detail'!$O$2:$O$518)

=SUMPRODUCT(--('Costs detail'!$Q$2:$Q518=DATE(2008,2,1)),--('Costs
detail'!$Q$2:$Q518<=DATE(2008,4,30)),'Costs detail'!$O$2:$O$518)


--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Thanks Very much (and noted the comment about different sized ranges). Is
there actually a way of doing the dates within the formula something like
this..

SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts
detail'!$O$2:$O$542)...ie so that within the formula I list the date
before
and after that I want to apply?

Thanks


"T. Valko" wrote:

One way...

A8 = start date = 2/1/2008
A9 = end date = 4/30/2008

=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs
detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs
detail'!$O$2:$O$518)

Note that in your posted formula you have different sized ranges:

$Q$2:$Q518
$O$2:$O$542



--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Hello

I would like to learn how to calculate amonts in a sumif that are
between
2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
.....where A8 is currently just a month reference (I mean it looks at
cell
A8
where I type in the month). So in other words, I'd like it to look up
values
in another worksheet called Costs Detail, providing the associated
dates
are
between, say 1st Feb and end of April. Any help always please.

spudsnruf
calculating between 2 given dates
 
That perfect. Worked a treat. Much appreciated

"T. Valko" wrote:

calculate amonts in a sumif that are between 2 dates.

In 99 out of 100 cases when someone says "between 2 dates" what they really
mean is greater than or equal to a start date and less than or equal to an
end date.

SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts
detail'!$O$2:$O$542)...

The logic of the above would *exclude* the following dates (m/d/y format):

2/1/2008
4/30/2008


Try one of these:

=SUMIF('Costs detail'!$Q$2:$Q518,"="&DATE(2008,2,1),'Costs
detail'!$O$2:$O$518)-SUMIF('Costs
detail'!$Q$2:$Q518,""&DATE(2008,4,30),'Costs
detail'!$O$2:$O$518)

=SUMPRODUCT(--('Costs detail'!$Q$2:$Q518=DATE(2008,2,1)),--('Costs
detail'!$Q$2:$Q518<=DATE(2008,4,30)),'Costs detail'!$O$2:$O$518)


--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Thanks Very much (and noted the comment about different sized ranges). Is
there actually a way of doing the dates within the formula something like
this..

SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts
detail'!$O$2:$O$542)...ie so that within the formula I list the date
before
and after that I want to apply?

Thanks


"T. Valko" wrote:

One way...

A8 = start date = 2/1/2008
A9 = end date = 4/30/2008

=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs
detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs
detail'!$O$2:$O$518)

Note that in your posted formula you have different sized ranges:

$Q$2:$Q518
$O$2:$O$542



--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Hello

I would like to learn how to calculate amonts in a sumif that are
between
2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
.....where A8 is currently just a month reference (I mean it looks at
cell
A8
where I type in the month). So in other words, I'd like it to look up
values
in another worksheet called Costs Detail, providing the associated
dates
are
between, say 1st Feb and end of April. Any help always please.

T. Valko
calculating between 2 given dates
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
That perfect. Worked a treat. Much appreciated

"T. Valko" wrote:

calculate amonts in a sumif that are between 2 dates.

In 99 out of 100 cases when someone says "between 2 dates" what they
really
mean is greater than or equal to a start date and less than or equal to
an
end date.

SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts
detail'!$O$2:$O$542)...

The logic of the above would *exclude* the following dates (m/d/y
format):

2/1/2008
4/30/2008


Try one of these:

=SUMIF('Costs detail'!$Q$2:$Q518,"="&DATE(2008,2,1),'Costs
detail'!$O$2:$O$518)-SUMIF('Costs
detail'!$Q$2:$Q518,""&DATE(2008,4,30),'Costs
detail'!$O$2:$O$518)

=SUMPRODUCT(--('Costs detail'!$Q$2:$Q518=DATE(2008,2,1)),--('Costs
detail'!$Q$2:$Q518<=DATE(2008,4,30)),'Costs detail'!$O$2:$O$518)


--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Thanks Very much (and noted the comment about different sized ranges).
Is
there actually a way of doing the dates within the formula something
like
this..

SUMIF('Costs detail'!$Q$2:$Q518,Date1,2,2008,Date<30,4,2008,'C osts
detail'!$O$2:$O$542)...ie so that within the formula I list the date
before
and after that I want to apply?

Thanks


"T. Valko" wrote:

One way...

A8 = start date = 2/1/2008
A9 = end date = 4/30/2008

=SUMIF('Costs detail'!$Q$2:$Q518,"="&A8,'Costs
detail'!$O$2:$O$518)-=SUMIF('Costs detail'!$Q$2:$Q518,""&A9,'Costs
detail'!$O$2:$O$518)

Note that in your posted formula you have different sized ranges:

$Q$2:$Q518
$O$2:$O$542



--
Biff
Microsoft Excel MVP


"spudsnruf" wrote in message
...
Hello

I would like to learn how to calculate amonts in a sumif that are
between
2
dates.

eg =SUMIF('Costs detail'!$Q$2:$Q518,A8,'Costs detail'!$O$2:$O$542)
.....where A8 is currently just a month reference (I mean it looks
at
cell
A8
where I type in the month). So in other words, I'd like it to look
up
values
in another worksheet called Costs Detail, providing the associated
dates
are
between, say 1st Feb and end of April. Any help always please.


All times are GMT +1. The time now is 01:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com