SUMPRODUCT to test two conditions

[Stan Brown]

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!

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Don Guillett]

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

 

[T. Valko]

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

 

[Stan Brown]

Sun, 13 Jan 2008 14:34:32 -0500 from T. Valko

Subject: Re: SUMPRODUCT to test two conditions
Newsgroups: microsoft.public.excel
Date: Sun, 13 Jan 2008 14:34:32 -0500
From: T. Valko <[email protected]>


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]

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.

 

[T. Valko]

Any chance you can remove that "-- " signature delimiter?

I could....if I knew how. It looks like OE automatically inserts that into
the sig. I could manually remove it every time I post but then I'd get
"tired" of doing that since I post quite a bit.


Biff
Microsoft Excel MVP

 

[Stan Brown]

Sun, 13 Jan 2008 17:03:56 -0500 from T. Valko

I could....if I knew how. It looks like OE automatically inserts that into
the sig. I could manually remove it every time I post but then I'd get
"tired" of doing that since I post quite a bit.

Thanks for the reply. I don't use OE so I can't advise you.

Sigh. I get SO tired of Microsoft software subverting Internet
standards.

 

[RagDyer]

I never realized that there was a purpose to those dashes ... until now.

If you want to go to the trouble, I'm told by one of the OE groups that the
only way is to use 3rd party software.

Here's a free, recommended one - OE-QuoteFix

http://home.in.tum.de/~jain/software/oe-quotefix/

 

[T. Valko]

If you want to go to the trouble

Nah, not really. I was thinking about looking in the OE groups for info on
the subject. Do you have a link to a thread?

 

[RagDyeR]

It seems the one I started pertaining to your question in this thread kinda
grew a little over there.

They do get into discussions ... much the same as happens here:

http://tinyurl.com/369pwo


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

If you want to go to the trouble

Nah, not really. I was thinking about looking in the OE groups for info on
the subject. Do you have a link to a thread?

 

[T. Valko]

They do get into discussions ... much the same as happens here:

I see!

Thanks for the link.

Well, I'm not going to change anything. If "my" sig messes up someone's
newsreader experience......oh well!

This little inconvenience is the same as top post vs bottom post.