"horizontal" criteria? NULL value?

[gjhicks]

Hello,

I use DSUM, etc a lot, which require a criteria range. Often I want to be able to copy such a formula down a column, that refers to values in cells in the same row as the formula.

But, it seems that the criteria range must be "column-wise".

Does anyone know if it possible to specify a "row-wise" criteria range?

Also, does anyone know how to set a cell to NULL, not zero or blank but actually nothing?

Regards,

Geoff.

 

[eliW]

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!

Book2
	A	B	C	D	E	F	G	H	I
1	CODE	QTY		CODE		CODE	QTY
2	300	1		300		300	37
3	305	2		CODE		301	4
4	300	3		301		302	21
5	301	4		CODE		303	13
6	303	5		302		304	34
7	300	6		CODE		305	11
8	304	7		303
9	303	8		CODE
10	305	9		304
11	302	10		CODE
12	302	11		305
13	304	12
14	300	13
15	300	14
16	304	15
17
18
19
20
Sheet1

 

[Aladin Akyurek]

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

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!

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

 

[Aladin Akyurek]

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)

DsumWithOffset.xls
	A	B	C	D	E	F	G
1	CODE	QTY		CODE		CODE	QTY
2	300	1		300		300	37
3	305	2		CODE		301	4
4	300	3		301		302	21
5	301	4		CODE		303	13
6	303	5		302		304	34
7	300	6		CODE		305	11
8	304	7		303
9	303	8		CODE
10	305	9		304
11	302	10		CODE
12	302	11		305
13	304	12
14	300	13
15	300	14
16	304	15
Sheet1

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

 

[Aladin Akyurek]

Method With implicit OR (Mine)

DsumWithORmethod.xls
	A	B	C	D	E	F	G
1	CODE	QTY				CODE	QTY
2	300	1				300	37
3	305	2				301	4
4	300	3				302	21
5	301	4				303	13
6	303	5				304	34
7	300	6				305	11
8	304	7
9	303	8
10	305	9
11	302	10
12	302	11
13	304	12
14	300	13
15	300	14
16	304	15
Sheet1

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

 

[Aladin Akyurek]

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

DsumWithDataTable.xls
	A	B	C	D	E	F	G
1	CODE	QTY		CODE		CODE	120
2	300	1				300	37
3	305	2				301	4
4	300	3				302	21
5	301	4				303	13
6	303	5				304	34
7	300	6				305	11
8	304	7
9	303	8
10	305	9
11	302	10
12	302	11
13	304	12
14	300	13
15	300	14
16	304	15
Sheet1

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.

 

[eliW]

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

 

[Aladin Akyurek]

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

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.

 

[gjhicks]

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

 

[daver676]

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

Dave