DSUM Criteria and Excel Help

[Guest]

I am trying to convert at old Lotus 123 spreadsheet to Excel. It is full of DSUM functions that fail because the old 123 criteria do not work. In the 123 method, I could specify criteria as
@dsum($datarange,"sales",region=B2
This would basically sum the "sales" column in datarange where the "region" column was equal to the value in cell B2. It looks like Excel can't handle this because the criteria must now be a range (though it looks like DSUM in access works like 123 did?) but I found the example and notes below under "criteria examples" in the Excel help on DSUM. I tried to figure out how to use it but I do not understand what it means (can't get it to work anyway). Can anyone translate what this means and whether this could solve my problem? I trying to avoid changing the many DSUMs to SUMIFs. Here is the snippet from the online help on criteria examples:

Conditions created as the result of a formul

You can use a calculated value that is the result of a formula as your criterion. When you use a formula to create a criterion, do not use a column label for a criteria label; either keep the criteria label blank or use a label that is not a column label in the list. For example, the following criteria range displays rows that have a value in column C greater than the average of cells C7:C10


=C7>AVERAGE($C$7:$C$10)

Note

The formula you use for a condition must use a relative reference to refer to the column label (for example, Sales) or the corresponding field in the first record. All other references in the formula must be absolute references, and the formula must evaluate to TRUE or FALSE. In the formula example, "C7" refers to the field (column C) for the first record (row 7) of the list

You can use a column label in the formula instead of a relative cell reference or a range name. When Microsoft Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion, you can ignore this error because it does not affect how the list is filtered

When evaluating data, Microsoft Excel does not distinguish between uppercase and lowercase characters

 

[Earl Kiosterud]

Anon,

You need to set up a criteria range (a little table somewhere on the sheet),
to which the DSUM refers. I'm not sure youve done that. It would look like
this, for your example:

F1:G2:
Sales Region
Northwest

If the table is in A1:B7:
Sales Region
10 NorthWest
20 NorthWest
15 NorthWest
25 SouthWest
13 NorthWest

Then the DSUM would look like this:
=DSUM(A1:B7, F1, F1:G2)

If you want sales totals for every region in the region column, make a pivot
table instead.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I am trying to convert at old Lotus 123 spreadsheet to Excel. It is full

of DSUM functions that fail because the old 123 criteria do not work. In
the 123 method, I could specify criteria as

@dsum($datarange,"sales",region=B2)
This would basically sum the "sales" column in datarange where the

"region" column was equal to the value in cell B2. It looks like Excel
can't handle this because the criteria must now be a range (though it looks
like DSUM in access works like 123 did?) but I found the example and notes
below under "criteria examples" in the Excel help on DSUM. I tried to
figure out how to use it but I do not understand what it means (can't get
it to work anyway). Can anyone translate what this means and whether this
could solve my problem? I trying to avoid changing the many DSUMs to
SUMIFs. Here is the snippet from the online help on criteria examples:

Conditions created as the result of a formula

You can use a calculated value that is the result of a formula as your

criterion. When you use a formula to create a criterion, do not use a column
label for a criteria label; either keep the criteria label blank or use a
label that is not a column label in the list. For example, the following
criteria range displays rows that have a value in column C greater than the
average of cells C7:C10.

=C7>AVERAGE($C$7:$C$10)

Notes

The formula you use for a condition must use a relative reference to refer

to the column label (for example, Sales) or the corresponding field in the
first record. All other references in the formula must be absolute
references, and the formula must evaluate to TRUE or FALSE. In the formula
example, "C7" refers to the field (column C) for the first record (row 7) of
the list.

You can use a column label in the formula instead of a relative cell

reference or a range name. When Microsoft Excel displays an error value such
as #NAME? or #VALUE! in the cell that contains the criterion, you can ignore
this error because it does not affect how the list is filtered.

When evaluating data, Microsoft Excel does not distinguish between

uppercase and lowercase characters.

 

[Aladin Akyurek]

See my contrib in: http://tinyurl.com/3e4xg

I am trying to convert at old Lotus 123 spreadsheet to Excel. It is full

of DSUM functions that fail because the old 123 criteria do not work. In
the 123 method, I could specify criteria as

@dsum($datarange,"sales",region=B2)
This would basically sum the "sales" column in datarange where the

"region" column was equal to the value in cell B2. It looks like Excel
can't handle this because the criteria must now be a range (though it looks
like DSUM in access works like 123 did?) but I found the example and notes
below under "criteria examples" in the Excel help on DSUM. I tried to
figure out how to use it but I do not understand what it means (can't get
it to work anyway). Can anyone translate what this means and whether this
could solve my problem? I trying to avoid changing the many DSUMs to
SUMIFs. Here is the snippet from the online help on criteria examples:

Conditions created as the result of a formula

You can use a calculated value that is the result of a formula as your

criterion. When you use a formula to create a criterion, do not use a column
label for a criteria label; either keep the criteria label blank or use a
label that is not a column label in the list. For example, the following
criteria range displays rows that have a value in column C greater than the
average of cells C7:C10.

=C7>AVERAGE($C$7:$C$10)

Notes

The formula you use for a condition must use a relative reference to refer

to the column label (for example, Sales) or the corresponding field in the
first record. All other references in the formula must be absolute
references, and the formula must evaluate to TRUE or FALSE. In the formula
example, "C7" refers to the field (column C) for the first record (row 7) of
the list.

You can use a column label in the formula instead of a relative cell

reference or a range name. When Microsoft Excel displays an error value such
as #NAME? or #VALUE! in the cell that contains the criterion, you can ignore
this error because it does not affect how the list is filtered.

When evaluating data, Microsoft Excel does not distinguish between

uppercase and lowercase characters.

 

[Harlan Grove]

I am trying to convert at old Lotus 123 spreadsheet to Excel. It is full of
DSUM functions that fail because the old 123 criteria do not work. . . .

Awkward choice of wording, 'old'. 123's in-formula criteria syntax is newer than
the criteria range syntax that was needed in 123 Release 2 (mid 1980s) and which
Excel copied.

. . . I could specify criteria as
@dsum($datarange,"sales",region=B2)

You could duplicate this exact functionality with

=SUMIF(INDEX(datarange,0,MATCH("region",INDEX(datarange,1,0),0)),B2,
INDEX(datarange,0,MATCH("sales",INDEX(datarange,1,0),0)))

However, it'd be a MUCH better idea to take the big step to using SQL.REQUEST.
If you'd already saved your workbook with the pathname C:\foo\bar.xls, and you'd
given the range of data the workbook-level defined name datarange, you could use
the formula

=SQL.REQUEST("DSN=Excel Files;DBQ=C:\foo\bar.xls",,,
"Select SUM(Sales) From datarange Where Region='"&B2&"'")

Read the online help entry for SQL.REQUEST for further details. Unless you were
using really perverse multiple table @DSUMs in 123, you should be able to
duplicate them all using SQL.REQUEST.