DSUM Criteria and Excel Help
Dsum Criteria
@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.
Earl Kiosterud
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
-------------------------------------------
"Dsum Criteria" <anon...@discussions.microsoft.com> wrote in message
news:2E85D0A0-8EF1-4BFD...@microsoft.com...
Aladin Akyurek
"Dsum Criteria" <anon...@discussions.microsoft.com> wrote in message
news:2E85D0A0-8EF1-4BFD...@microsoft.com...
Harlan Grove
>I am trying to convert at old Lotus 123 spreadsheet to Excel. It is full of
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.
--
To top-post is human, to bottom-post and snip is sublime.