Excel DSUM w/o criteria range on worksheet

[RRinTetons]

I want to embed some 48 DSUM results in a grid format report. I can certainly create 48 separate criteria ranges in a hidden part of the worksheet and put all the ranges there, but I was wondering if there's a way to embed the criteria range directly in the function rather than referencing a range on the worksheet.

e.g. formula reads =DSUM(CurrentYearData,"NetPosting",R37:U38)

where the criteria is in the range R37:U38.

Is there a syntax that would allow me to say

=DSUM(CurrentYearData,"NetPosting",<something>)

that would accomplish the same thing?

-
Richard Ray
Jackson Hole Mountain Resort

 

[Loomah]

i've tried to do this on may occasions and could never find a way! doesn't mean it can't be done but i can't find a way (if one exists) of adding a multi dimensional array into a formula which is basically what would be required to hold headings and values for the criteria.

choices are to either:
- bite the bullet and have your sheet full of all the different criteria you need. i've done that before named ranges and all - quite tedious but worth it!
- explore alternatives to d functions. sumproduct is the most obvious alternative

there may, however, be performance issues to consider depending on the size of your source data. also considerations may be needed for how dynamic you need your end result to be.

hope this gives you some help along the way...

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​