Sum of Columns with Calendar Dates and Data

[FBS]

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

 

[Bob Phillips]

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

--

HTH

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

 

[FBS]

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

 

[Bob Phillips]

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]

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.

 

[Max]

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

 

[Bob Phillips]

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]

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.

 

[FBS]

You guys are awsome, Thank you very much
Cheers

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

 

[Max]

You're welcome !
Thanks for the feedback ..

 

[Bob Phillips]

and the coffee time

You're welcome !
Thanks for the feedback ..

 

[Max]

and the coffee time
but of course <g>