| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with conditions | Excel Discussion (Misc queries) | |||
| SUMPRODUCT with conditions | Excel Worksheet Functions | |||
| Conditions in sumproduct | Excel Worksheet Functions | |||
| sumproduct three conditions | Excel Worksheet Functions | |||
| Can wildcards be used in SUMPRODUCT conditions | Excel Worksheet Functions | |||
Linear Mode
