excel database statistical functions (DSUM etc..)
 

Scenario: A budget table with some of the following fields; Company, Project,
ExpenseType, and Amount to keep things simple.

Is their a way from within the excel's database statistical functions
formula to define the criteria by means of referring to the names of the data
fields themselves to form a dynamic and complex set of criteria instead of
defining hundreds of external criteria ranges that would be needed to define
every single possibility? Something like the following:
=DSUM(mytable,"Amount",+Company=a2,Project=b2,Expe nseType=c2) where this
formula could be copied down to refer to the company, project, and expense
type that was appropriate for the next row at row 3 vs. row 2 in the above
example.

Instead of having to do this: =DSUM(mytable,"Amount",CriteriaRange)
Thanks
Craig

excel database statistical functions (DSUM etc..)
 

The criteria range needs a heading row and criteria row(s), so won't
work in the way you'd like.

You could use a pivot table to summarize the data, as described in
Excel's Help, or he

http://www.contextures.com/xlPivot01.html

Or, use the Sumproduct function, as described he

http://www.contextures.com/xlFunctio...tml#SumProduct

CraigS wrote:
Scenario: A budget table with some of the following fields; Company, Project,
ExpenseType, and Amount to keep things simple.

Is their a way from within the excel's database statistical functions
formula to define the criteria by means of referring to the names of the data
fields themselves to form a dynamic and complex set of criteria instead of
defining hundreds of external criteria ranges that would be needed to define
every single possibility? Something like the following:
=DSUM(mytable,"Amount",+Company=a2,Project=b2,Expe nseType=c2) where this
formula could be copied down to refer to the company, project, and expense
type that was appropriate for the next row at row 3 vs. row 2 in the above
example.

Instead of having to do this: =DSUM(mytable,"Amount",CriteriaRange)
Thanks
Craig



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html