Caculating Columns Between Certain Dates

Yeah, let's concentrate on getting something working Dermot.

First, enter the dates in A2:A6 in the format 03/02/2005. Dots will only
work if you have some special setting.

Secondly, in the formula, when referring to a date cell you don't need to
coerce nit to a date (It is already done) and you don't need quotes, and
similarly for testing the company name test as a cell, so it is just

=SUMPRODUCT(--(A2:A6=G3,(A2:A6,F3= H3),--(B2:B6=F3),(C2:C6+D26))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dermot" wrote in message
...
Hi Bob.
A few more questions now that I have had a proper look at the information.

I have entered the following formula into and appropriate cell on a small
test sheet I made upexacly as specified. Then I tried it modified ,to
refer
to cells which contain the dates as below, but no joy...get an error
message.

=SUMPRODUCT(--(A2:A6= --"=G3",(A2:A6,F3 = --
"=H3"),--(B2:B6="=F3"),(C2:C6+D26))

G3 is the start date cell, H3 is the End Date Cell, F3 is the company
cell.

I formatted A2 to A6 to date format 3.2.2005 am not sure if this is
correct
as when the error is generated a blue rectangle appears arround the date
column.

Quote
If you know the periods, that is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.

How whould I do incorporate all start, end dates for all quarters.
Maybbe a bit ahead of myself here when I haven't got the supplied equation
to work yet!!

Regards
Dermot

"Bob Phillips" wrote:


"Dermot" wrote in message
...
Thanks for the reply Bob,
I have looked up the SUMPRODUCT Function in my excel inside out book
to
better.
I have yet to try the formula but wonder if you could clarify a few
questions for me?

1. Can you write a verbal interpretation of the formula you have
supplied
me
so I can better understand the reasoning behind it?


See http://www.xldynamic.com/source/xld.SUMPRODUCT.html


2. Using your supplied formula. Can it be place in any cell or does it
have
to be a specific cell to ensure the correct cell references?


No, as long as your refer back to the correct cells, it can be placed
anywhere.


3. The Dates you have entered. How are these entered? Can they be
changed
for different periods throughout the year? Does the individual using
the
spreadsheet need to modify the formula or just enter a start date and
end
date, and then the calculation would be automatic? The intended users
are
not
too familiar with excel, and I am not sure what I can and cannot do
with
excel regarding this problem.


What I gave you was two example dates to test between. It can be
changed to
any two dates that you want. I used ISO standard date formats,
yyyy-mm-dd,
either change the date such as --"2005-01-01" by something other date,
or
put the date in a cell and refer to that. If you know the periods, that
is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.


I do need to find a solution Bob and appreciate your contribution. You
supplied me with a brief formla for another problem, which resolved my
problem, but it took me a while before I undestood what I was doing.


I have just Googled that. I replied to something on 8th Nov, and I see
that
you responded 10 days later. I didn't see that in the NGs. Do you still
need
help on that one?

It is http://tinyurl.com/da27l

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dermot" wrote in message
...
Thanks for the link Bob ( Lots to get through here) and explanation.

I have tried to find the posting dated the 8-nov but I always find it
difficult searching for previous postings if I have overlooked keeping my
notification email. Can you send me a link to it?
Thanks
Dermot

"Bob Phillips" wrote:


"Dermot" wrote in message
...
Thanks for the reply Bob,
I have looked up the SUMPRODUCT Function in my excel inside out book
to
better.
I have yet to try the formula but wonder if you could clarify a few
questions for me?

1. Can you write a verbal interpretation of the formula you have
supplied
me
so I can better understand the reasoning behind it?


See http://www.xldynamic.com/source/xld.SUMPRODUCT.html


2. Using your supplied formula. Can it be place in any cell or does it
have
to be a specific cell to ensure the correct cell references?


No, as long as your refer back to the correct cells, it can be placed
anywhere.


3. The Dates you have entered. How are these entered? Can they be
changed
for different periods throughout the year? Does the individual using
the
spreadsheet need to modify the formula or just enter a start date and
end
date, and then the calculation would be automatic? The intended users
are
not
too familiar with excel, and I am not sure what I can and cannot do
with
excel regarding this problem.


What I gave you was two example dates to test between. It can be
changed to
any two dates that you want. I used ISO standard date formats,
yyyy-mm-dd,
either change the date such as --"2005-01-01" by something other date,
or
put the date in a cell and refer to that. If you know the periods, that
is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.


I do need to find a solution Bob and appreciate your contribution. You
supplied me with a brief formla for another problem, which resolved my
problem, but it took me a while before I undestood what I was doing.


I have just Googled that. I replied to something on 8th Nov, and I see
that
you responded 10 days later. I didn't see that in the NGs. Do you still
need
help on that one?

Hi Bob,
I missed this posting and posted a lot of rubbish in the two postings at the
end. Please ignore them.

I will review all the information you have given me todate.

Is it okay with you, for me to post back here, if I have any further
questions relating to this problem?

Regards
Dermot

"Bob Phillips" wrote:

Yeah, let's concentrate on getting something working Dermot.

First, enter the dates in A2:A6 in the format 03/02/2005. Dots will only
work if you have some special setting.

Secondly, in the formula, when referring to a date cell you don't need to
coerce nit to a date (It is already done) and you don't need quotes, and
similarly for testing the company name test as a cell, so it is just

=SUMPRODUCT(--(A2:A6=G3,(A2:A6,F3= H3),--(B2:B6=F3),(C2:C6+D26))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dermot" wrote in message
...
Hi Bob.
A few more questions now that I have had a proper look at the information.

I have entered the following formula into and appropriate cell on a small
test sheet I made upexacly as specified. Then I tried it modified ,to
refer
to cells which contain the dates as below, but no joy...get an error
message.

=SUMPRODUCT(--(A2:A6= --"=G3",(A2:A6,F3 = --
"=H3"),--(B2:B6="=F3"),(C2:C6+D26))

G3 is the start date cell, H3 is the End Date Cell, F3 is the company
cell.

I formatted A2 to A6 to date format 3.2.2005 am not sure if this is
correct
as when the error is generated a blue rectangle appears arround the date
column.

Quote
If you know the periods, that is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.

How whould I do incorporate all start, end dates for all quarters.
Maybbe a bit ahead of myself here when I haven't got the supplied equation
to work yet!!

Regards
Dermot

"Bob Phillips" wrote:


"Dermot" wrote in message
...
Thanks for the reply Bob,
I have looked up the SUMPRODUCT Function in my excel inside out book
to
better.
I have yet to try the formula but wonder if you could clarify a few
questions for me?

1. Can you write a verbal interpretation of the formula you have
supplied
me
so I can better understand the reasoning behind it?


See http://www.xldynamic.com/source/xld.SUMPRODUCT.html


2. Using your supplied formula. Can it be place in any cell or does it
have
to be a specific cell to ensure the correct cell references?


No, as long as your refer back to the correct cells, it can be placed
anywhere.


3. The Dates you have entered. How are these entered? Can they be
changed
for different periods throughout the year? Does the individual using
the
spreadsheet need to modify the formula or just enter a start date and
end
date, and then the calculation would be automatic? The intended users
are
not
too familiar with excel, and I am not sure what I can and cannot do
with
excel regarding this problem.


What I gave you was two example dates to test between. It can be
changed to
any two dates that you want. I used ISO standard date formats,
yyyy-mm-dd,
either change the date such as --"2005-01-01" by something other date,
or
put the date in a cell and refer to that. If you know the periods, that
is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.


I do need to find a solution Bob and appreciate your contribution. You
supplied me with a brief formla for another problem, which resolved my
problem, but it took me a while before I undestood what I was doing.


I have just Googled that. I replied to something on 8th Nov, and I see
that
you responded 10 days later. I didn't see that in the NGs. Do you still
need
help on that one?

Of course, I will keep watching it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dermot" wrote in message
...
Hi Bob,
I missed this posting and posted a lot of rubbish in the two postings at
the
end. Please ignore them.

I will review all the information you have given me todate.

Is it okay with you, for me to post back here, if I have any further
questions relating to this problem?

Regards
Dermot

"Bob Phillips" wrote:

Yeah, let's concentrate on getting something working Dermot.

First, enter the dates in A2:A6 in the format 03/02/2005. Dots will only
work if you have some special setting.

Secondly, in the formula, when referring to a date cell you don't need
to
coerce nit to a date (It is already done) and you don't need quotes,
and
similarly for testing the company name test as a cell, so it is just

=SUMPRODUCT(--(A2:A6=G3,(A2:A6,F3= H3),--(B2:B6=F3),(C2:C6+D26))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dermot" wrote in message
...
Hi Bob.
A few more questions now that I have had a proper look at the
information.

I have entered the following formula into and appropriate cell on a
small
test sheet I made upexacly as specified. Then I tried it modified ,to
refer
to cells which contain the dates as below, but no joy...get an error
message.

=SUMPRODUCT(--(A2:A6= --"=G3",(A2:A6,F3 = --
"=H3"),--(B2:B6="=F3"),(C2:C6+D26))

G3 is the start date cell, H3 is the End Date Cell, F3 is the company
cell.

I formatted A2 to A6 to date format 3.2.2005 am not sure if this is
correct
as when the error is generated a blue rectangle appears arround the
date
column.

Quote
If you know the periods, that is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.

How whould I do incorporate all start, end dates for all quarters.
Maybbe a bit ahead of myself here when I haven't got the supplied
equation
to work yet!!

Regards
Dermot

"Bob Phillips" wrote:


"Dermot" wrote in message
...
Thanks for the reply Bob,
I have looked up the SUMPRODUCT Function in my excel inside out
book
to
better.
I have yet to try the formula but wonder if you could clarify a
few
questions for me?

1. Can you write a verbal interpretation of the formula you have
supplied
me
so I can better understand the reasoning behind it?


See http://www.xldynamic.com/source/xld.SUMPRODUCT.html


2. Using your supplied formula. Can it be place in any cell or
does it
have
to be a specific cell to ensure the correct cell references?


No, as long as your refer back to the correct cells, it can be
placed
anywhere.


3. The Dates you have entered. How are these entered? Can they be
changed
for different periods throughout the year? Does the individual
using
the
spreadsheet need to modify the formula or just enter a start date
and
end
date, and then the calculation would be automatic? The intended
users
are
not
too familiar with excel, and I am not sure what I can and cannot
do
with
excel regarding this problem.


What I gave you was two example dates to test between. It can be
changed to
any two dates that you want. I used ISO standard date formats,
yyyy-mm-dd,
either change the date such as --"2005-01-01" by something other
date,
or
put the date in a cell and refer to that. If you know the periods,
that
is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.


I do need to find a solution Bob and appreciate your contribution.
You
supplied me with a brief formla for another problem, which
resolved my
problem, but it took me a while before I undestood what I was
doing.


I have just Googled that. I replied to something on 8th Nov, and I
see
that
you responded 10 days later. I didn't see that in the NGs. Do you
still
need
help on that one?

Hi Bob,
I have read through and have obtained a great understanding of the
versatility of "SUMPRODUCT" Thanks again. The link explains all very clearly.

One further question regarding the information on the linked
page.....although it is not relevent to the initial question in this
topic....I do not know any other way to explain the question...please
advise.....
Question
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I noticed that the tables i.e. Table 2 ......use a double border effect on
the cells, I wondered if this had been applied from within excel....I can't
find a method to do it in excel....or were the examples in this tutorial
produced in another application?
Thanks
Dermot

"Bob Phillips" wrote:


"Dermot" wrote in message
...
Thanks for the reply Bob,
I have looked up the SUMPRODUCT Function in my excel inside out book to
better.
I have yet to try the formula but wonder if you could clarify a few
questions for me?

1. Can you write a verbal interpretation of the formula you have supplied
me
so I can better understand the reasoning behind it?


See http://www.xldynamic.com/source/xld.SUMPRODUCT.html


2. Using your supplied formula. Can it be place in any cell or does it
have
to be a specific cell to ensure the correct cell references?


No, as long as your refer back to the correct cells, it can be placed
anywhere.


3. The Dates you have entered. How are these entered? Can they be changed
for different periods throughout the year? Does the individual using the
spreadsheet need to modify the formula or just enter a start date and end
date, and then the calculation would be automatic? The intended users are
not
too familiar with excel, and I am not sure what I can and cannot do with
excel regarding this problem.


What I gave you was two example dates to test between. It can be changed to
any two dates that you want. I used ISO standard date formats, yyyy-mm-dd,
either change the date such as --"2005-01-01" by something other date, or
put the date in a cell and refer to that. If you know the periods, that is
if they are fixed, you could code all of them directly into the spreadsheet,
saving the users any need to change it.


I do need to find a solution Bob and appreciate your contribution. You
supplied me with a brief formla for another problem, which resolved my
problem, but it took me a while before I undestood what I was doing.


I have just Googled that. I replied to something on 8th Nov, and I see that
you responded 10 days later. I didn't see that in the NGs. Do you still need
help on that one?

Hi Dermot,

No, that is jsut an HTML table border. All hand-crafted with TextPad.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dermot" wrote in message
...
Hi Bob,
I have read through and have obtained a great understanding of the
versatility of "SUMPRODUCT" Thanks again. The link explains all very
clearly.

One further question regarding the information on the linked
page.....although it is not relevent to the initial question in this
topic....I do not know any other way to explain the question...please
advise.....
Question
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I noticed that the tables i.e. Table 2 ......use a double border effect on
the cells, I wondered if this had been applied from within excel....I
can't
find a method to do it in excel....or were the examples in this tutorial
produced in another application?
Thanks
Dermot

"Bob Phillips" wrote:


"Dermot" wrote in message
...
Thanks for the reply Bob,
I have looked up the SUMPRODUCT Function in my excel inside out book
to
better.
I have yet to try the formula but wonder if you could clarify a few
questions for me?

1. Can you write a verbal interpretation of the formula you have
supplied
me
so I can better understand the reasoning behind it?


See http://www.xldynamic.com/source/xld.SUMPRODUCT.html


2. Using your supplied formula. Can it be place in any cell or does it
have
to be a specific cell to ensure the correct cell references?


No, as long as your refer back to the correct cells, it can be placed
anywhere.


3. The Dates you have entered. How are these entered? Can they be
changed
for different periods throughout the year? Does the individual using
the
spreadsheet need to modify the formula or just enter a start date and
end
date, and then the calculation would be automatic? The intended users
are
not
too familiar with excel, and I am not sure what I can and cannot do
with
excel regarding this problem.


What I gave you was two example dates to test between. It can be
changed to
any two dates that you want. I used ISO standard date formats,
yyyy-mm-dd,
either change the date such as --"2005-01-01" by something other date,
or
put the date in a cell and refer to that. If you know the periods, that
is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.


I do need to find a solution Bob and appreciate your contribution. You
supplied me with a brief formla for another problem, which resolved my
problem, but it took me a while before I undestood what I was doing.


I have just Googled that. I replied to something on 8th Nov, and I see
that
you responded 10 days later. I didn't see that in the NGs. Do you still
need
help on that one?

Thanks Bob

Best wishes
Dermot

"Bob Phillips" wrote:

Hi Dermot,

No, that is jsut an HTML table border. All hand-crafted with TextPad.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dermot" wrote in message
...
Hi Bob,
I have read through and have obtained a great understanding of the
versatility of "SUMPRODUCT" Thanks again. The link explains all very
clearly.

One further question regarding the information on the linked
page.....although it is not relevent to the initial question in this
topic....I do not know any other way to explain the question...please
advise.....
Question
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I noticed that the tables i.e. Table 2 ......use a double border effect on
the cells, I wondered if this had been applied from within excel....I
can't
find a method to do it in excel....or were the examples in this tutorial
produced in another application?
Thanks
Dermot

"Bob Phillips" wrote:


"Dermot" wrote in message
...
Thanks for the reply Bob,
I have looked up the SUMPRODUCT Function in my excel inside out book
to
better.
I have yet to try the formula but wonder if you could clarify a few
questions for me?

1. Can you write a verbal interpretation of the formula you have
supplied
me
so I can better understand the reasoning behind it?


See http://www.xldynamic.com/source/xld.SUMPRODUCT.html


2. Using your supplied formula. Can it be place in any cell or does it
have
to be a specific cell to ensure the correct cell references?


No, as long as your refer back to the correct cells, it can be placed
anywhere.


3. The Dates you have entered. How are these entered? Can they be
changed
for different periods throughout the year? Does the individual using
the
spreadsheet need to modify the formula or just enter a start date and
end
date, and then the calculation would be automatic? The intended users
are
not
too familiar with excel, and I am not sure what I can and cannot do
with
excel regarding this problem.


What I gave you was two example dates to test between. It can be
changed to
any two dates that you want. I used ISO standard date formats,
yyyy-mm-dd,
either change the date such as --"2005-01-01" by something other date,
or
put the date in a cell and refer to that. If you know the periods, that
is
if they are fixed, you could code all of them directly into the
spreadsheet,
saving the users any need to change it.


I do need to find a solution Bob and appreciate your contribution. You
supplied me with a brief formla for another problem, which resolved my
problem, but it took me a while before I undestood what I was doing.


I have just Googled that. I replied to something on 8th Nov, and I see
that
you responded 10 days later. I didn't see that in the NGs. Do you still
need
help on that one?

Hi Bob
I sent an email to your email address not sure if you accept emails from newsgroup posters?
I attached my excel attempt to understand SUMPRODUCT....but I haven't
managed to get it to work for me yet.
Would you be able to send me a working example as discussed in this posting?
Thanks
Dermot


"Dermot" wrote:

I have a spread sheet with for simplicity 4 columns
Column 1 is a Date Column,
Column 2 is a Company Name,
Column 3 is a Payment amount X
Column 4 is a Payment amount Y

The amount due is the sum of X+Y between the relevant dates.

The entries are in date order and the Company names random.
The number of entries per company varies also.
I want to be able to calculate the amount due for each company over a thee
month period between certain varied dates.

Questions
1. Is there a way to enter the dates to calculate between.
2. What is the best way to perform the calculations for each company?

An example or link to an example would be appreciated.
Regards
Dermot

Do you realize that you have just posted Bob's e-mail address on the web?

Why do you think that we disguise our addresses?
So that we love to get TONS of spam?

IMHO, you have done him a *GREAT* disservice ! ! !
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dermot" wrote in message
...
Hi Bob
I sent an email to your email address not sure if you accept emails from newsgroup posters?
I attached my excel attempt to understand SUMPRODUCT....but I haven't
managed to get it to work for me yet.
Would you be able to send me a working example as discussed in this
posting?
Thanks
Dermot


"Dermot" wrote:

I have a spread sheet with for simplicity 4 columns
Column 1 is a Date Column,
Column 2 is a Company Name,
Column 3 is a Payment amount X
Column 4 is a Payment amount Y

The amount due is the sum of X+Y between the relevant dates.

The entries are in date order and the Company names random.
The number of entries per company varies also.
I want to be able to calculate the amount due for each company over a
thee
month period between certain varied dates.

Questions
1. Is there a way to enter the dates to calculate between.
2. What is the best way to perform the calculations for each company?

An example or link to an example would be appreciated.
Regards
Dermot

Yes I noticed what I did after the event...a bit thoughtless
Sorry about this Bob
Regards
Dermot

"Ragdyer" wrote:

Do you realize that you have just posted Bob's e-mail address on the web?

Why do you think that we disguise our addresses?
So that we love to get TONS of spam?

IMHO, you have done him a *GREAT* disservice ! ! !
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dermot" wrote in message
...
Hi Bob
I sent an email to your email address not sure if you accept emails from newsgroup posters?
I attached my excel attempt to understand SUMPRODUCT....but I haven't
managed to get it to work for me yet.
Would you be able to send me a working example as discussed in this
posting?
Thanks
Dermot


"Dermot" wrote:

I have a spread sheet with for simplicity 4 columns
Column 1 is a Date Column,
Column 2 is a Company Name,
Column 3 is a Payment amount X
Column 4 is a Payment amount Y

The amount due is the sum of X+Y between the relevant dates.

The entries are in date order and the Company names random.
The number of entries per company varies also.
I want to be able to calculate the amount due for each company over a
thee
month period between certain varied dates.

Questions
1. Is there a way to enter the dates to calculate between.
2. What is the best way to perform the calculations for each company?

An example or link to an example would be appreciated.
Regards
Dermot