Sumproduct; two conditions; no list
 

I have a list that is broken up in different parts. The list is broken up by
blank cells and by text.

My formula works if I only include a part of the sheet

=SUMPRODUCT((Sheet1!A5:A17=A3)*(Sheet1!T5:T17="Gew erbe")*Sheet1!K5:K17)

but not if I include the whole column:

=SUMPRODUCT((Sheet1!A:A=A3)*(Sheet1!T:T="Gewerbe") *Sheet1!K:K)

Any suggestions?

Sumproduct; two conditions; no list
 

Unless you are fortunate enough to have Excel 2007, array function like
SUMPRODUCT do not permit full-column/full-row references. So your SUMPRODUCT
is behaving as expected.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Verlaesslichkeit" wrote in
message ...
I have a list that is broken up in different parts. The list is broken up
by
blank cells and by text.

My formula works if I only include a part of the sheet

=SUMPRODUCT((Sheet1!A5:A17=A3)*(Sheet1!T5:T17="Gew erbe")*Sheet1!K5:K17)

but not if I include the whole column:

=SUMPRODUCT((Sheet1!A:A=A3)*(Sheet1!T:T="Gewerbe") *Sheet1!K:K)

Any suggestions?

Sumproduct; two conditions; no list
 

hi
in versions 2003 and earlier, sumproduct doesn't work if you use the entire
column.
A:A doesn't work but A1: A65534 does (1 row short of the entilre column).
in 2007, you can use the entire column.

regards
FSt1


"Verlaesslichkeit" wrote:

I have a list that is broken up in different parts. The list is broken up by
blank cells and by text.

My formula works if I only include a part of the sheet

=SUMPRODUCT((Sheet1!A5:A17=A3)*(Sheet1!T5:T17="Gew erbe")*Sheet1!K5:K17)

but not if I include the whole column:

=SUMPRODUCT((Sheet1!A:A=A3)*(Sheet1!T:T="Gewerbe") *Sheet1!K:K)

Any suggestions?

Sumproduct; two conditions; no list
 

Using SUMPRODUCT pre 2007 you cannot use the whole column reference, you
have to use the cell reference A1:A65536 not A:A


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.thecodecage.com/forumz/member.php?userid=111
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=70721

Sumproduct; two conditions; no list
 

The problem is not the entire column. In row thirty I have text in columns A,
T and K. This is when the formula starts giving me a value error. Is there
any way to change the formula so it works for the whole list? Thanks!

=SUMPRODUCT((Sheet1!A10:A30=A3)*(Sheet1!T10:T30="G ewerbe")*Sheet1!K10:K30)
"FSt1" wrote:

hi
in versions 2003 and earlier, sumproduct doesn't work if you use the entire
column.
A:A doesn't work but A1: A65534 does (1 row short of the entilre column).
in 2007, you can use the entire column.

regards
FSt1


"Verlaesslichkeit" wrote:

I have a list that is broken up in different parts. The list is broken up by
blank cells and by text.

My formula works if I only include a part of the sheet

=SUMPRODUCT((Sheet1!A5:A17=A3)*(Sheet1!T5:T17="Gew erbe")*Sheet1!K5:K17)

but not if I include the whole column:

=SUMPRODUCT((Sheet1!A:A=A3)*(Sheet1!T:T="Gewerbe") *Sheet1!K:K)

Any suggestions?

Sumproduct; two conditions; no list
 

OK. Assume A30 equals A3 and T30 equal "Gewerbe". So these conditions are
satisfied. Now K30, as you stated, contains text (let's assume "abc"). What
result would you like when you multiply "abc" by 1? To Excel, this is a
#Value error. If you don't want #Value, tell us what you want instead.

Regards,
Fred.

"Verlaesslichkeit" wrote in
message ...
The problem is not the entire column. In row thirty I have text in columns
A,
T and K. This is when the formula starts giving me a value error. Is there
any way to change the formula so it works for the whole list? Thanks!

=SUMPRODUCT((Sheet1!A10:A30=A3)*(Sheet1!T10:T30="G ewerbe")*Sheet1!K10:K30)
"FSt1" wrote:

hi
in versions 2003 and earlier, sumproduct doesn't work if you use the
entire
column.
A:A doesn't work but A1: A65534 does (1 row short of the entilre column).
in 2007, you can use the entire column.

regards
FSt1


"Verlaesslichkeit" wrote:

I have a list that is broken up in different parts. The list is broken
up by
blank cells and by text.

My formula works if I only include a part of the sheet

=SUMPRODUCT((Sheet1!A5:A17=A3)*(Sheet1!T5:T17="Gew erbe")*Sheet1!K5:K17)

but not if I include the whole column:

=SUMPRODUCT((Sheet1!A:A=A3)*(Sheet1!T:T="Gewerbe") *Sheet1!K:K)

Any suggestions?

Sumproduct; two conditions; no list
 

When you use multiplication inside of =sumproduct(), each argument has to be
numeric. (True/False is coerced to 1/0 by the multiplication.)

But you could use this syntax:

=SUMPRODUCT(--(Sheet1!A10:A30=A3),
--(Sheet1!T10:T30="Gewerbe"),
(Sheet1!K10:K30))

And text in column K10:K30 will be ignored.

Kind of like the difference how text is treated in:
=a1+a2+a3
vs.
=sum(a1:a3)



Verlaesslichkeit wrote:

The problem is not the entire column. In row thirty I have text in columns A,
T and K. This is when the formula starts giving me a value error. Is there
any way to change the formula so it works for the whole list? Thanks!

=SUMPRODUCT((Sheet1!A10:A30=A3)*(Sheet1!T10:T30="G ewerbe")*Sheet1!K10:K30)
"FSt1" wrote:

hi
in versions 2003 and earlier, sumproduct doesn't work if you use the entire
column.
A:A doesn't work but A1: A65534 does (1 row short of the entilre column).
in 2007, you can use the entire column.

regards
FSt1


"Verlaesslichkeit" wrote:

I have a list that is broken up in different parts. The list is broken up by
blank cells and by text.

My formula works if I only include a part of the sheet

=SUMPRODUCT((Sheet1!A5:A17=A3)*(Sheet1!T5:T17="Gew erbe")*Sheet1!K5:K17)

but not if I include the whole column:

=SUMPRODUCT((Sheet1!A:A=A3)*(Sheet1!T:T="Gewerbe") *Sheet1!K:K)

Any suggestions?

--

Dave Peterson

Sumproduct; two conditions; no list
 

A30 does not equal A3 and T30 does not equal "Gewerbe". I only want the sum
of the VALUES where BOTH conditions are met. Thanks for your understanding

"Fred Smith" wrote:

OK. Assume A30 equals A3 and T30 equal "Gewerbe". So these conditions are
satisfied. Now K30, as you stated, contains text (let's assume "abc"). What
result would you like when you multiply "abc" by 1? To Excel, this is a
#Value error. If you don't want #Value, tell us what you want instead.

Regards,
Fred.

"Verlaesslichkeit" wrote in
message ...
The problem is not the entire column. In row thirty I have text in columns
A,
T and K. This is when the formula starts giving me a value error. Is there
any way to change the formula so it works for the whole list? Thanks!

=SUMPRODUCT((Sheet1!A10:A30=A3)*(Sheet1!T10:T30="G ewerbe")*Sheet1!K10:K30)
"FSt1" wrote:

hi
in versions 2003 and earlier, sumproduct doesn't work if you use the
entire
column.
A:A doesn't work but A1: A65534 does (1 row short of the entilre column).
in 2007, you can use the entire column.

regards
FSt1


"Verlaesslichkeit" wrote:

I have a list that is broken up in different parts. The list is broken
up by
blank cells and by text.

My formula works if I only include a part of the sheet

=SUMPRODUCT((Sheet1!A5:A17=A3)*(Sheet1!T5:T17="Gew erbe")*Sheet1!K5:K17)

but not if I include the whole column:

=SUMPRODUCT((Sheet1!A:A=A3)*(Sheet1!T:T="Gewerbe") *Sheet1!K:K)

Any suggestions?