"horizontal" criteria? NULL value?

gjhicks

New Member
Joined
Aug 19, 2003
Messages
2
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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,931
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
ABCDEFGHI
1CODEQTYCODECODEQTY
2300130030037
33052CODE3014
4300330130221
53014CODE30313
6303530230434
73006CODE30511
83047303
93038CODE
103059304
1130210CODE
1230211305
1330412
1430013
1530014
1630415
17
18
19
20
Sheet1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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
ABCDEFG
1CODEQTYCODECODEQTY
2300130030037
33052CODE3014
4300330130221
53014CODE30313
6303530230434
73006CODE30511
83047303
93038CODE
103059304
1130210CODE
1230211305
1330412
1430013
1530014
1630415
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

Method With implicit OR (Mine)
DsumWithORmethod.xls
ABCDEFG
1CODEQTYCODEQTY
2300130037
330523014
4300330221
5301430313
6303530434
7300630511
83047
93038
103059
1130210
1230211
1330412
1430013
1530014
1630415
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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
ABCDEFG
1CODEQTYCODECODE120
2300130037
330523014
4300330221
5301430313
6303530434
7300630511
83047
93038
103059
1130210
1230211
1330412
1430013
1530014
1630415
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

Well-known Member
Joined
Mar 2, 2002
Messages
1,931

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

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.
 

daver676

Board Regular
Joined
Sep 9, 2003
Messages
70
Can I test for two criteria values (match part number and date shipped) using any of the above options? Please Explain. Thanks.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,109,258
Messages
5,527,660
Members
409,781
Latest member
Maxcwy2020

This Week's Hot Topics

Top