Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Disjoint range for DSUM criteria
Hi all. Is it possible to use a disjoint range for the criteria parameter of
the DSUM function? I have a worksheet tab (called Data) that contains my list. It looks like the following: Year Name Value Region ----- ------- ------ --------- 2001 Revenue 10 East 2001 Expense 20 East 2002 Revenue 30 West 2003 Expense 40 North .... I have worksheets for each region (East, West, North, South) which have a cross tab that looks like: 2001 2002 ... Revenue Expense .... I have a criteria range set up for each year on the same row that repeats all the same values except for the Year... L M N O P Q R S T --- --- --- --- --- --- --- --- --- Year Name Region Year Name Region Year Name Region 2001 Revenue East 2002 Revenue East 2003 Revenue East Year Name Region Year Name Region Year Name Region 2001 Expense East 2002 Expense East 2003 Expense East The crosstab table has DSUM formulas as follows: 2001 2002 ... Revenue dsum(Data,Value,L1:N2) dsum(Data,Value,O1:Q2) ... Expense dsum(Data,Value,L3:N4) dsum(Data,Value,O3:Q4) ... .... I would like to be able to reduce the repeating information by having something like: L M N O P --- --- --- --- --- Year Year Year Name Region 2001 2002 2003 Revenue East and having my criteria look like: 2001 2002 ... Revenue dsum(Data,Value,(L1:L2,O1:P2)) dsum(Data,Value,(M1:M2,O1:P2)) .... .... Is this possible? Thanks in advance. |
#2
|
|||
|
|||
"Jim Bennett" wrote...
.... I would like to be able to reduce the repeating information by having something like: L M N O P --- --- --- --- --- Year Year Year Name Region 2001 2002 2003 Revenue East and having my criteria look like: 2001 2002 Revenue dsum(Data,Value,(L1:L2,O1:P2)) dsum(Data,Value,(M1:M2,O1:P2)) Is this possible? No, which is one of the reasons never to use DSUM, DCOUNT, etc. in Excel since they're now VERY ANCIENT and nearly useless. Summing with multiple conditions is easier (though perhaps not quite as fast) using SUMPRODUCT. =SUMPRODUCT((INDEX(Data,0,Yr)=2001)*(INDEX(Data,0, Nm)="Revenue") *(INDEX(Data,0,Rg)="East"),INDEX(Data,0,Value)) where Yr, Nm and Rg are the column indices of the Year, Name and Region columns the Data range. Or you could use MATCH, so instead of (INDEX(Data,0,Yr)=2001) you'd use (INDEX(Data,0,MATCH("Year",INDEX(Data,1,0),0))=200 1) Alternatively, you could use SQL.REQUEST, but it has memory leak issues if you need to use many such formulas. |
#3
|
|||
|
|||
No, which is one of the reasons never to use DSUM, DCOUNT, etc. in Excel
since they're now VERY ANCIENT and nearly useless. Summing with multiple conditions is easier (though perhaps not quite as fast) using SUMPRODUCT. =SUMPRODUCT((INDEX(Data,0,Yr)=2001)*(INDEX(Data,0, Nm)="Revenue") *(INDEX(Data,0,Rg)="East"),INDEX(Data,0,Value)) where Yr, Nm and Rg are the column indices of the Year, Name and Region columns the Data range. Or you could use MATCH, so instead of (INDEX(Data,0,Yr)=2001) you'd use (INDEX(Data,0,MATCH("Year",INDEX(Data,1,0),0))=200 1) Alternatively, you could use SQL.REQUEST, but it has memory leak issues if you need to use many such formulas. The only good thing about D functions is that if you learn them you learn how to handle the advanced filter which has similar criteria ranges -- Regards, Peo Sjoblom (No private emails please) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
How do I count data in range A:A that is dependent upon criteria . | Excel Worksheet Functions | |||
Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions | |||
use a date range as criteria in a countif formula | Excel Worksheet Functions | |||
Specify a null value in an Excel Database criteria range | Excel Worksheet Functions |