Sumproduct; two conditions; no list

[Verlaesslichkeit]

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="Gewerbe")*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?

 

[Bernard Liengme]

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

 

[FSt1]

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

 

[oldchippy]

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

 

[Verlaesslichkeit]

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="Gewerbe")*Sheet1!K10:K30)

 

[Fred Smith]

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.

 

[Dave Peterson]

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]

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