Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct-condition as a range
Hello,
I'm having trouble with a formula using sumproduct. I've been using http://www.xldynamic.com/source/xld.SUMPRODUCT.html as a guide, and it's been great. Everything works fine when I use a single criteria or single cell reference in the conditions in the forumlas, but I want to use a range in my second condition, and am getting #Value as a return. I have queried 3 columns A, B, and C on one tab. A are account numbers, B are sub account numbers, and C are amounts. On another tab, I have a range of sub accounts that I want excluded from my results. I can't seem to get this set up properly. So far I have =SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable Costs'!$B$2:$B$2000 <A8:A37),('Allocable Costs'!$C$2:$C$2000)) which returns #N/A. If I only reference one cell in my second condition, it works fine, but with the range of <A8:A37, I'm getting an error. From the website above I found example 3 at the bottom of the page and tried =SUMPRODUCT(--('Allocable Costs'!$A$2:$A$2000=Final!B56),--('Allocable Costs'!$B$2:$B$2000 <TRANSPOSE(A8:A37)),('Allocable Costs'!$C$2:$C$2000)) but that returns #Value. Am I using this incorrectly? I'm assuming I need to use the Transpose because my criteria is in a column and not a row, but am at a loss. Any guidance appreciated. Thanks, AMH |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT with an IF condition? | Excel Discussion (Misc queries) | |||
Multiple Lookup as condition in sumproduct formula | Excel Worksheet Functions | |||
sumproduct and colored cell as a condition to be met | Excel Worksheet Functions | |||
Sumproduct: condition with ? | Excel Discussion (Misc queries) | |||
Sumproduct with condition??? | Excel Discussion (Misc queries) |