Disjoint range for DSUM criteria

[Jim Bennett]

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,O12)) dsum(Data,Value,(M1:M2,O12))
....
....

Is this possible?

Thanks in advance.

 

[Harlan Grove]

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,O12)) dsum(Data,Value,(M1:M2,O12))

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))=2001)

Alternatively, you could use SQL.REQUEST, but it has memory leak issues if
you need to use many such formulas.

 

[Peo Sjoblom]

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))=2001)

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)