Sumproduct; two conditions; no list

  • Thread starter Verlaesslichkeit
  • Start date
V

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?
 
B

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
 
F

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
 
O

oldchippy

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

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

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

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top