Sum of Columns with Calendar Dates and Data
 

I have two columns in excel that gets its data from an access database. The
two columns are Calendar dates and the other numbers, I would like to have a
formula in a second worksheet that looks up a range of dates i.e.; March
Sales and displays a sum of the corresponding figures in the second column.--
do it right, do it once

=SUMPRODUCT(--(MONTH(Sheet1!A1:A100)=1),Sheet1!B1:B100)

--

HTH

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


"FBS" wrote in message
...
I have two columns in excel that gets its data from an access database.
The
two columns are Calendar dates and the other numbers, I would like to have
a
formula in a second worksheet that looks up a range of dates i.e.; March
Sales and displays a sum of the corresponding figures in the second
column.--
do it right, do it once

Thank you Bob for the quick responce, the formula gives me an error
#NAME?
what is the cause? any thoughts?

"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(Sheet1!A1:A100)=1),Sheet1!B1:B100)

--

HTH

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


"FBS" wrote in message
...
I have two columns in excel that gets its data from an access database.
The
two columns are Calendar dates and the other numbers, I would like to have
a
formula in a second worksheet that looks up a range of dates i.e.; March
Sales and displays a sum of the corresponding figures in the second
column.--
do it right, do it once

Not really, it does work I tested it.

#NAME usually means that it doesn't recognise one on the words, and there is
only SUMPRODUCT and MONTH in there that it might not. Check that there are
no spurious - that crept into the copy, there should only be two, just
before the (MONTH

--

HTH

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


"FBS" wrote in message
...
Thank you Bob for the quick responce, the formula gives me an error
#NAME?
what is the cause? any thoughts?

"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(Sheet1!A1:A100)=1),Sheet1!B1:B100)

--

HTH

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


"FBS" wrote in message
...
I have two columns in excel that gets its data from an access
database.
The
two columns are Calendar dates and the other numbers, I would like to
have
a
formula in a second worksheet that looks up a range of dates i.e.;
March
Sales and displays a sum of the corresponding figures in the second
column.--
do it right, do it once

Right Bob it does work, Thanks
At first I wanted to select the whole column as a range as in this sample
=SUMPRODUCT(--(MONTH(Sheet2!A:A)=1),Sheet2!E:E), this did not work, I had to
fix cell position as shown in your sample above.
I wanted the select the cell column because the Data that I am refreshing
"from an external source" is set to change with every refresh. Looks I will
have to fix this in another way so the data stays put in the same cell of the
excel worksheet.

"Bob Phillips" wrote:

Not really, it does work I tested it.

#NAME usually means that it doesn't recognise one on the words, and there is
only SUMPRODUCT and MONTH in there that it might not. Check that there are
no spurious - that crept into the copy, there should only be two, just
before the (MONTH

--

HTH

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


"FBS" wrote in message
...
Thank you Bob for the quick responce, the formula gives me an error
#NAME?
what is the cause? any thoughts?

"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(Sheet1!A1:A100)=1),Sheet1!B1:B100)

--

HTH

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


"FBS" wrote in message
...
I have two columns in excel that gets its data from an access
database.
The
two columns are Calendar dates and the other numbers, I would like to
have
a
formula in a second worksheet that looks up a range of dates i.e.;
March
Sales and displays a sum of the corresponding figures in the second
column.--
do it right, do it once

=SUMPRODUCT(--(MONTH(Sheet2!A:A)=1),Sheet2!E:E),
this did not work

Entire col references aren't accepted in SUMPRODUCT

But you could use something like:

=SUMPRODUCT(--(MONTH(Sheet2!A1:A65535)=1),Sheet2!E1:E65535)

albeit this comes with quite a performance hit (slow calcs)

Maybe more viable if you were to reduce the "65535" in the formula to the
*smallest* max number of data rows expected in cols A and E in Sheet2, say
to 10000 or so ??

Another way to play it, if there's *no blank rows* in between the source
data, is to use dynamic ranges for cols A and E in Sheet2

Click Insert Name Define

Under Names in workbook, enter a name: Mth (say)

Put in the "Refers to" box :
=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A))

Click Add
(this creates a dynamic range for col A in Sheet2)

Repeat the steps above to make
another dynamic range for col E in Sheet2, viz.:

Clear and input for the name: Val (say)
Refers to:
=OFFSET(Sheet2!$E$1,,,COUNTA(Sheet2!$E:$E))

Now you can use this formula instead:
=SUMPRODUCT(--(MONTH(Mth)=1),Val)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"FBS" wrote in message
...
Right Bob it does work, Thanks
At first I wanted to select the whole column as a range as in this sample
=SUMPRODUCT(--(MONTH(Sheet2!A:A)=1),Sheet2!E:E), this did not work, I had
to
fix cell position as shown in your sample above.
I wanted the select the cell column because the Data that I am refreshing
"from an external source" is set to change with every refresh. Looks I
will
have to fix this in another way so the data stays put in the same cell of
the
excel worksheet.

Although I would do it the way that Max suggested, with a named range, you
can actually bypass that step, by using the dynamic range directly, i.e.

OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A))

=SUMPRODUCT(--(MONTH(OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A))) =1),OFFSET(S
heet2!$E$1,,,COUNTA(Sheet2!$A:$A)))

This does introduce another 2 functions (as it would with a named range),
which will slow things down, so it is debatable whether that is quicker than
putting in a large max row.

--

HTH

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


"Max" wrote in message
...
=SUMPRODUCT(--(MONTH(Sheet2!A:A)=1),Sheet2!E:E),
this did not work

Entire col references aren't accepted in SUMPRODUCT

But you could use something like:

=SUMPRODUCT(--(MONTH(Sheet2!A1:A65535)=1),Sheet2!E1:E65535)

albeit this comes with quite a performance hit (slow calcs)

Maybe more viable if you were to reduce the "65535" in the formula to the
*smallest* max number of data rows expected in cols A and E in Sheet2, say
to 10000 or so ??

Another way to play it, if there's *no blank rows* in between the source
data, is to use dynamic ranges for cols A and E in Sheet2

Click Insert Name Define

Under Names in workbook, enter a name: Mth (say)

Put in the "Refers to" box :
=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A))

Click Add
(this creates a dynamic range for col A in Sheet2)

Repeat the steps above to make
another dynamic range for col E in Sheet2, viz.:

Clear and input for the name: Val (say)
Refers to:
=OFFSET(Sheet2!$E$1,,,COUNTA(Sheet2!$E:$E))

Now you can use this formula instead:
=SUMPRODUCT(--(MONTH(Mth)=1),Val)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"FBS" wrote in message
...
Right Bob it does work, Thanks
At first I wanted to select the whole column as a range as in this
sample
=SUMPRODUCT(--(MONTH(Sheet2!A:A)=1),Sheet2!E:E), this did not work, I
had
to
fix cell position as shown in your sample above.
I wanted the select the cell column because the Data that I am
refreshing
"from an external source" is set to change with every refresh. Looks I
will
have to fix this in another way so the data stays put in the same cell
of
the
excel worksheet.

"Bob Phillips" wrote
Although I would do it the way that Max suggested,
with a named range, ...

... um usually, I might just opt to take the performance hit and go it with
the direct 65535 route, with calc mode set to manual. Gives me a good excuse
to take a break each time calc is needed .. press F9, then off I go to enjoy
the break !! <bg Cheers.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You guys are awsome, Thank you very much
Cheers

"Max" wrote:

"Bob Phillips" wrote
Although I would do it the way that Max suggested,
with a named range, ...

... um usually, I might just opt to take the performance hit and go it with
the direct 65535 route, with calc mode set to manual. Gives me a good excuse
to take a break each time calc is needed .. press F9, then off I go to enjoy
the break !! <bg Cheers.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"FBS" wrote in message
...
You guys are awsome, Thank you very much
Cheers

and the coffee time :-)

"Max" wrote in message
...
You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"FBS" wrote in message
...
You guys are awsome, Thank you very much
Cheers

and the coffee time :-)
but of course <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----