"horizontal" criteria? NULL value?

I think I can understand your question - I had once a similar problem!

I didn't find a way to enter the criteria "row-wise", and if entering it "column-wise" you can't copy the formula down and get the desired results.

But I came up with a little "trick" that enables to copy the formula along the rows down. But you have to setup your criterias the way I shoe in column D

HTH

Eli

BTW if someone has a better way this would be nice!

eliW said:

I think I can understand your question - I had once a similar problem!

I didn't find a way to enter the criteria "row-wise", and if entering it "column-wise" you can't copy the formula down and get the desired results.

But I came up with a little "trick" that enables to copy the formula along the rows down. But you have to setup your criterias the way I shoe in column D

Click to expand...

Eli,

Yet another "approach" to make DSUM downwards copiable for a set of changing criterion values. That's interesting.

The formula

=DSUM(data1,$B$1,OFFSET(D1,ROW()-2,0,2,1))

is not robust against any row insertion before the formula area. A remedy would be...

=DSUM(data1,$B$1,OFFSET(D1,(ROW()-ROW($G$2)+2)-2,0,2,1))

BTW if someone has a better way this would be nice!

Click to expand...

As I alluded to above, there are a few. All have peculiar drawbacks of their own.

Eli,

Here the methods we have, that is, I know of, including yours...

Note. The range A1:B16 is named data1 in following up your example.

Method With Offset (EliW)

The Dsum formula in G2...

=DSUM(data1,$B$1,OFFSET(D1,(ROW()-ROW($G$2)+2)-2,0,2,1))

reads of the appropriate criteria from the range in D using the OFFSET bit.

See next post for Method With implicit OR

Method With implicit OR (Mine)

The Dsum formula in G2 is...

=DSUM(data1,$B$1,$F$1:F2)-SUM($G$1:G1)

See next post for Method With a Data Table

Method With a Data Table (MS Knowldedge Base)

This has been used a few times at this board (sadly, without a reference to the source). It can be found at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;282851

D1:D2 is the criteria range, with D2 empty.

In F2 enter: CODE (Relevant field from data1)

In G2 enter:

=DSUM(data1,$B$1,$D$1:$D$2)

Get the relevant codes under F1, using Advanced Filter for example.

Now, this is important,...

Select F1:G7.
Activate Data|Table.
Type D2 in the Column Input Cell box.

Excel will immediately fill up the range G2:G7 with desired computations.

There is also mentioned another method using data validation (Dave Hawley) I'm not familiar with.

It seems to me that...

Yours requires repeating the field of interest and might be expensive because of the volatile OFFSET.

Mine, although it does not repeat the field of interest, does lots of extraneous computations. I was hoping that the speed attributed to DSUM would compensate. However, DanExcel who applied this method to a huge range reported to me that the performance gets degraded.

I have no idea how the Data Table method performs.

Hi Aladin,
Its amazing how this can be done in so many creative ways!

But for this particular question; it seems that all other ways are not approaching directly the problem of the CRITERIA format in DSUM, but are using different computations to achieve the same result.

If that is so - why bother at all to use DSUM when one can easily get the same result with the "ordinary" SUMIF???

Regards,

Eli

eliW said:

Hi Aladin,
Its amazing how this can be done in so many creative ways!

But for this particular question; it seems that all other ways are not approaching directly the problem of the CRITERIA format in DSUM, but are using different computations to achieve the same result.

If that is so - why bother at all to use DSUM when one can easily get the same result with the "ordinary" SUMIF???

Regards,

Eli

Click to expand...

You're right about SumIf. The issue arises when we want multiconditional computations. The choices are formulas that operate on computed arrays [SumProduct and array-entered (matrix) formulas], formulas with Dfunctions [because of their (presumed?) efficiency], and pivot tables. It seems to me that Dfunctions are not that flexible, and they seem to be less efficient and have a lesser scope than the ones in Lotus 1-2-3, an oft-heard claim at ms newsgroups.

It can even be judicious to concatenate ranges to which one or more conditions apply (spending cell space) in order to reduce a multiconditional summing into one with a single condition so that a SumIf formula can be used.

Thanks a lot, I reckon I have a number of methods to try out!

Can I test for two criteria values (match part number and date shipped) using any of the above options? Please Explain. Thanks.

Dave