excel database statistical functions (DSUM etc..)

[Guest]

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,ExpenseType=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]

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 here:

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

Or, use the Sumproduct function, as described here:

http://www.contextures.com/xlFunctions01.html#SumProduct