In my checking-account spreadsheet, I have
A4 and below - date
E4 and below - deposit amount
H4 and below - comment
B2 - today's date or as-of date
(Rows 1 to 3 of columns A and E are just text headers, not numbers or
formulas.)

I want to summarize interest for the current year (year that matches
B2's year, comment equals "Interest"). The following array formula
works, but I have to remember to update it when transactions go below
row 699:

{ =SUM( (YEAR(A4:A699)=YEAR(B$2))*(H4:H699="Interest")*E4: E699 ) }

It seems this should be doable with a sumproduct, so I type:

=SUMPRODUCT(E:E, --(YEAR(A:A)=YEAR($B$2)), --(H:H="Interest"))

but I get a #NUM. When I change it to

=SUMPRODUCT(E4:E, --(YEAR(A4:A)=YEAR($B$2)), --(H4:H="Interest"))

I get #NAME.

Making the references absolute didn't help:

=SUMPRODUCT($E:$E, --(YEAR($A:$A)=YEAR($B$2)), --($H:$H="Interest"))

still gets #NUM.

What am I doing wrong, and what's the solution? Thanks!

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

SUMPROUCT(), like array formulas, cannot work with whole columns. Try
making the whole columns ranges even if it is B4:A65535 but having such a
large range may slow your sheet down somewhat.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Stan Brown" <the_stan_brown@fastmail.fm> wrote in message
news:MPG.21f3de679ac9710c98b39d@news.individual.ne t...
> In my checking-account spreadsheet, I have
> A4 and below - date
> E4 and below - deposit amount
> H4 and below - comment
> B2 - today's date or as-of date
> (Rows 1 to 3 of columns A and E are just text headers, not numbers or
> formulas.)
>
> I want to summarize interest for the current year (year that matches
> B2's year, comment equals "Interest"). The following array formula
> works, but I have to remember to update it when transactions go below
> row 699:
>
> { =SUM( (YEAR(A4:A699)=YEAR(B$2))*(H4:H699="Interest")*E4: E699 ) }
>
> It seems this should be doable with a sumproduct, so I type:
>
> =SUMPRODUCT(E:E, --(YEAR(A:A)=YEAR($B$2)), --(H:H="Interest"))
>
> but I get a #NUM. When I change it to
>
> =SUMPRODUCT(E4:E, --(YEAR(A4:A)=YEAR($B$2)), --(H4:H="Interest"))
>
> I get #NAME.
>
> Making the references absolute didn't help:
>
> =SUMPRODUCT($E:$E, --(YEAR($A:$A)=YEAR($B$2)), --($H:$H="Interest"))
>
> still gets #NUM.
>
> What am I doing wrong, and what's the solution? Thanks!
>
> --
> Stan Brown, Oak Road Systems, Tompkins County, New York, USA
> http://OakRoadSystems.com/
> "If there's one thing I know, it's men. I ought to: it's
> been my life work." -- Marie Dressler, in /Dinner at Eight/
>

SUMPROUCT(), like array formulas, cannot work with whole columns. Try
making the whole columns ranges even if it is B4:A65535 but having such a
large range may slow your sheet down somewhat.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Stan Brown" <the_stan_brown@fastmail.fm> wrote in message
news:MPG.21f3de679ac9710c98b39d@news.individual.ne t...
> In my checking-account spreadsheet, I have
> A4 and below - date
> E4 and below - deposit amount
> H4 and below - comment
> B2 - today's date or as-of date
> (Rows 1 to 3 of columns A and E are just text headers, not numbers or
> formulas.)
>
> I want to summarize interest for the current year (year that matches
> B2's year, comment equals "Interest"). The following array formula
> works, but I have to remember to update it when transactions go below
> row 699:
>
> { =SUM( (YEAR(A4:A699)=YEAR(B$2))*(H4:H699="Interest")*E4: E699 ) }
>
> It seems this should be doable with a sumproduct, so I type:
>
> =SUMPRODUCT(E:E, --(YEAR(A:A)=YEAR($B$2)), --(H:H="Interest"))
>
> but I get a #NUM. When I change it to
>
> =SUMPRODUCT(E4:E, --(YEAR(A4:A)=YEAR($B$2)), --(H4:H="Interest"))
>
> I get #NAME.
>
> Making the references absolute didn't help:
>
> =SUMPRODUCT($E:$E, --(YEAR($A:$A)=YEAR($B$2)), --($H:$H="Interest"))
>
> still gets #NUM.
>
> What am I doing wrong, and what's the solution? Thanks!
>
> --
> Stan Brown, Oak Road Systems, Tompkins County, New York, USA
> http://OakRoadSystems.com/
> "If there's one thing I know, it's men. I ought to: it's
> been my life work." -- Marie Dressler, in /Dinner at Eight/
>

try this
=SUMPRODUCT(--(YEAR(A2:A22)=YEAR($B$2),--(H2:H22="Interest"),e2:e22)
or
=SUMPRODUCT((YEAR(A2:A22)=YEAR($B$2)*(H2:H22="Inte rest")*e2:e22)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Stan Brown" <the_stan_brown@fastmail.fm> wrote in message
news:MPG.21f3de679ac9710c98b39d@news.individual.ne t...
> In my checking-account spreadsheet, I have
> A4 and below - date
> E4 and below - deposit amount
> H4 and below - comment
> B2 - today's date or as-of date
> (Rows 1 to 3 of columns A and E are just text headers, not numbers or
> formulas.)
>
> I want to summarize interest for the current year (year that matches
> B2's year, comment equals "Interest"). The following array formula
> works, but I have to remember to update it when transactions go below
> row 699:
>
> { =SUM( (YEAR(A4:A699)=YEAR(B$2))*(H4:H699="Interest")*E4: E699 ) }
>
> It seems this should be doable with a sumproduct, so I type:
>
> =SUMPRODUCT(E:E, --(YEAR(A:A)=YEAR($B$2)), --(H:H="Interest"))
>
> but I get a #NUM. When I change it to
>
> =SUMPRODUCT(E4:E, --(YEAR(A4:A)=YEAR($B$2)), --(H4:H="Interest"))
>
> I get #NAME.
>
> Making the references absolute didn't help:
>
> =SUMPRODUCT($E:$E, --(YEAR($A:$A)=YEAR($B$2)), --($H:$H="Interest"))
>
> still gets #NUM.
>
> What am I doing wrong, and what's the solution? Thanks!
>
> --
> Stan Brown, Oak Road Systems, Tompkins County, New York, USA
> http://OakRoadSystems.com/
> "If there's one thing I know, it's men. I ought to: it's
> been my life work." -- Marie Dressler, in /Dinner at Eight/

try this
=SUMPRODUCT(--(YEAR(A2:A22)=YEAR($B$2),--(H2:H22="Interest"),e2:e22)
or
=SUMPRODUCT((YEAR(A2:A22)=YEAR($B$2)*(H2:H22="Inte rest")*e2:e22)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Stan Brown" <the_stan_brown@fastmail.fm> wrote in message
news:MPG.21f3de679ac9710c98b39d@news.individual.ne t...
> In my checking-account spreadsheet, I have
> A4 and below - date
> E4 and below - deposit amount
> H4 and below - comment
> B2 - today's date or as-of date
> (Rows 1 to 3 of columns A and E are just text headers, not numbers or
> formulas.)
>
> I want to summarize interest for the current year (year that matches
> B2's year, comment equals "Interest"). The following array formula
> works, but I have to remember to update it when transactions go below
> row 699:
>
> { =SUM( (YEAR(A4:A699)=YEAR(B$2))*(H4:H699="Interest")*E4: E699 ) }
>
> It seems this should be doable with a sumproduct, so I type:
>
> =SUMPRODUCT(E:E, --(YEAR(A:A)=YEAR($B$2)), --(H:H="Interest"))
>
> but I get a #NUM. When I change it to
>
> =SUMPRODUCT(E4:E, --(YEAR(A4:A)=YEAR($B$2)), --(H4:H="Interest"))
>
> I get #NAME.
>
> Making the references absolute didn't help:
>
> =SUMPRODUCT($E:$E, --(YEAR($A:$A)=YEAR($B$2)), --($H:$H="Interest"))
>
> still gets #NUM.
>
> What am I doing wrong, and what's the solution? Thanks!
>
> --
> Stan Brown, Oak Road Systems, Tompkins County, New York, USA
> http://OakRoadSystems.com/
> "If there's one thing I know, it's men. I ought to: it's
> been my life work." -- Marie Dressler, in /Dinner at Eight/

>I have to remember to update it when transactions go below row 699:

You can't use entire columns as range references unless you're using Excel
2007.

You can either use a range large enough to allow for future entries or you
could use a dynamic range. A dynamic range would be the best choice.

http://contextures.com/xlNames01.html#Dynamic


--
Biff
Microsoft Excel MVP


"Stan Brown" <the_stan_brown@fastmail.fm> wrote in message
news:MPG.21f3de679ac9710c98b39d@news.individual.ne t...
> In my checking-account spreadsheet, I have
> A4 and below - date
> E4 and below - deposit amount
> H4 and below - comment
> B2 - today's date or as-of date
> (Rows 1 to 3 of columns A and E are just text headers, not numbers or
> formulas.)
>
> I want to summarize interest for the current year (year that matches
> B2's year, comment equals "Interest"). The following array formula
> works, but I have to remember to update it when transactions go below
> row 699:
>
> { =SUM( (YEAR(A4:A699)=YEAR(B$2))*(H4:H699="Interest")*E4: E699 ) }
>
> It seems this should be doable with a sumproduct, so I type:
>
> =SUMPRODUCT(E:E, --(YEAR(A:A)=YEAR($B$2)), --(H:H="Interest"))
>
> but I get a #NUM. When I change it to
>
> =SUMPRODUCT(E4:E, --(YEAR(A4:A)=YEAR($B$2)), --(H4:H="Interest"))
>
> I get #NAME.
>
> Making the references absolute didn't help:
>
> =SUMPRODUCT($E:$E, --(YEAR($A:$A)=YEAR($B$2)), --($H:$H="Interest"))
>
> still gets #NUM.
>
> What am I doing wrong, and what's the solution? Thanks!
>
> --
> Stan Brown, Oak Road Systems, Tompkins County, New York, USA
> http://OakRoadSystems.com/
> "If there's one thing I know, it's men. I ought to: it's
> been my life work." -- Marie Dressler, in /Dinner at Eight/

>I have to remember to update it when transactions go below row 699:

You can't use entire columns as range references unless you're using Excel
2007.

You can either use a range large enough to allow for future entries or you
could use a dynamic range. A dynamic range would be the best choice.

http://contextures.com/xlNames01.html#Dynamic


--
Biff
Microsoft Excel MVP


"Stan Brown" <the_stan_brown@fastmail.fm> wrote in message
news:MPG.21f3de679ac9710c98b39d@news.individual.ne t...
> In my checking-account spreadsheet, I have
> A4 and below - date
> E4 and below - deposit amount
> H4 and below - comment
> B2 - today's date or as-of date
> (Rows 1 to 3 of columns A and E are just text headers, not numbers or
> formulas.)
>
> I want to summarize interest for the current year (year that matches
> B2's year, comment equals "Interest"). The following array formula
> works, but I have to remember to update it when transactions go below
> row 699:
>
> { =SUM( (YEAR(A4:A699)=YEAR(B$2))*(H4:H699="Interest")*E4: E699 ) }
>
> It seems this should be doable with a sumproduct, so I type:
>
> =SUMPRODUCT(E:E, --(YEAR(A:A)=YEAR($B$2)), --(H:H="Interest"))
>
> but I get a #NUM. When I change it to
>
> =SUMPRODUCT(E4:E, --(YEAR(A4:A)=YEAR($B$2)), --(H4:H="Interest"))
>
> I get #NAME.
>
> Making the references absolute didn't help:
>
> =SUMPRODUCT($E:$E, --(YEAR($A:$A)=YEAR($B$2)), --($H:$H="Interest"))
>
> still gets #NUM.
>
> What am I doing wrong, and what's the solution? Thanks!
>
> --
> Stan Brown, Oak Road Systems, Tompkins County, New York, USA
> http://OakRoadSystems.com/
> "If there's one thing I know, it's men. I ought to: it's
> been my life work." -- Marie Dressler, in /Dinner at Eight/

Sun, 13 Jan 2008 14:34:32 -0500 from T. Valko
<biffinpitt@comcast.net>:
> Subject: Re: SUMPRODUCT to test two conditions
> Newsgroups: microsoft.public.excel
> Date: Sun, 13 Jan 2008 14:34:32 -0500
> From: T. Valko <biffinpitt@comcast.net>
> Message-ID: <e31ASthVIHA.1188@TK2MSFTNGP04.phx.gbl>
>
> >I have to remember to update it when transactions go below row 699:
>
> You can't use entire columns as range references unless you're using Excel
> 2007.

Ah -- that was the piece I was missing. Thanks!

Any chance you can remove that "-- " signature delimiter? It's
appropriate when what follows is just signature, but since you're
posting your comments and your signature before the quoted material
that makes the whole quote get treated like part of your signature.
Newsreaders that honor the delimiter then throw away the entire
previous quote because signatures aren't supposed to be quoted.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Sun, 13 Jan 2008 14:34:32 -0500 from T. Valko
<biffinpitt@comcast.net>:
> Subject: Re: SUMPRODUCT to test two conditions
> Newsgroups: microsoft.public.excel
> Date: Sun, 13 Jan 2008 14:34:32 -0500
> From: T. Valko <biffinpitt@comcast.net>
> Message-ID: <e31ASthVIHA.1188@TK2MSFTNGP04.phx.gbl>
>
> >I have to remember to update it when transactions go below row 699:
>
> You can't use entire columns as range references unless you're using Excel
> 2007.

Ah -- that was the piece I was missing. Thanks!

Any chance you can remove that "-- " signature delimiter? It's
appropriate when what follows is just signature, but since you're
posting your comments and your signature before the quoted material
that makes the whole quote get treated like part of your signature.
Newsreaders that honor the delimiter then throw away the entire
previous quote because signatures aren't supposed to be quoted.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Sun, 13 Jan 2008 13:56:37 -0000 from Sandy Mann <sandymann2
@mailinator.com>:
> SUMPROUCT(), like array formulas, cannot work with whole columns. Try
> making the whole columns ranges even if it is B4:A65535 but having such a
> large range may slow your sheet down somewhat.

Thanks. I had thought about that but like you I was concerned about
performance. I didn't benchmark it, though.

T. Valko's suggestion of dynamic ranges did the trick.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/