Free Microsoft Excel 2013 Quick Reference

Using dsum Results

Thanks for all help.
I need to put the value of the status bar sum into a variable. How do I
access the status bar sum value?
Someone said use Dsum=application.sum(selection), but I need the value in
the status bar, not what is selected (I am using autofilter).
Thanks.

Using Excel XP.
===============
I'm setting up a budget workbook, and one sheet (5 Year Strategic Plan) has
a DSUM in it under a column labeled "2007":

=DSUM(Capital,'Capital Projects'!$P$3,$E$39:$F$42)*12+DSUM(Capital,'Capital
Projects'!$P$3,$V$17:$W$23)

It shows "zero" as the value pulled, yet when I look at the sheet labeled
"Capital Projects", I see 2 values that should have been pulled (just dummy
data for now): $1500 on Jan 2007 and $2000 on June 2007.

$E$39:$F$42 corresponds to: Capital Timing
Land Monthly
Buildings Monthly
Vehicles Monthly

and $V$17:$W$23 corresponds to:
Capital Timing
Land 2007
Land Is not monthly
Buildings 2007
Buildings Is not monthly
Vehicles 2007
Vehicles Is not monthly

I've done this many times before in this workbook, so Im don't know where I
went wrong. Any ideas/comments?

--
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a 501 (c)(3) conservation non-profit organization

The formula causing me fits is "=DSUM(Grants,Grants!$E$10,C74:D75)*12"
(without the quotes); it's from a budget worksheet I'm adapting for our
org's use.

I keep getting a "#VALUE" error. When I simplify it down to retrieving a
single value from the worksheet, it does fine. But a DSUM causes the error
every time. I've used the forumla checker but it runs out to everything in
parentheses and then gives the error. I verified in Insert > Names > Define
that the cell range is the worksheet I need and includes just the cells I
need - to include the Totals row at the bottom and the column headers at the
top (single row). I don't know how to further "dissect" the DSUM to find
the specific problem. Maybe I should delete the name and then re-create it
...... ? Any ideas?
--
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a 501 (c)(3) conservation non-profit organization

I'm having a problem with the criteria for a DSUM formual on a worksheet;
the workbook is a budget worksheet I got from a large non-profit that I'm
learnign and (hopefully) going to adapt for our use.
The worksheet in question is named Cash Budget. One of the expense items is
named "Conf./Training/Meeting". The column names come from another sheet
called "Chart of Accounts" and are labeled "Jan -07", Feb-07", etc. Hiden
above each is the rest of the criteria; cell 1 has the word "Timing" and
cell 2 has the word "monthly". The formula is:

=DSUM(Conservation,'Conservation
Projects'!$O$3,B1:B3)+DSUM(Research,'Research
Projects'!$L$3,B1:B3)+DSUM(Outreach,'Outreach
Projects'!$L$3,B1:B3)+DSUM(Development,'Development
Projects'!$L$3,B1:B3)+DSUM(Administration,'Administration
Projects'!$O$3,B1:B3)+DSUM(Capital,'Capital Projects'!$L$3,B1:B3)

The error I get is "#VALUE!" (without the qoutes). When I do a trace error,
the problem is with the "DSUM(Research,'Research Projects'!$L$3,B1:B3)" part
of the formula. I rechecked the database name, the field name, the cell,
and the criteria - even redid each and than saved the file - same results.
I then made it simple - I deleted all but the problem portion - got the same
error and it points to "B1:B3: every time. I then changed it to other
databases and fields - the formula works fine.

I can't figure out how or why this is not working. Any ideas our there? (My
experience level is between Novice and Intermediate.)
--
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a 501 (c)(3) conservation non-profit organization

The Googles have done nothing for me as far as this question goes, so I come to you all on my knees, begging for some clarity.

I have a large spreadsheet of data, with two columns containing the criteria I'd like to use to sum the rest of the data columns. Call these columns "Parlour" and "Toppings". The data columns show the number of parlours of a certain type that offer the corresponding topping for that row. Hence, there might be 5 Pizza Huts that offer Pineapple in one city (column), but only 2 Pizza Huts that offer Goat Cheese.

On another sheet in the workbook, I have a list of parlours, and I want to know, for each parlour, how many locations in a given city offer a certain combination of toppings. My thought was to create a criteria range for the toppings, and a separate list of the parlours, so that the DSUM criteria would merge the ranges somehow. The idea would be to build in the ability to pull the formula down for any parlours that are added to the list, so that the toppings criteria stays the same, but the parlour criteria range is referenced at an offset position, corresponding to the new listing.

Does this make sense? I can't seem to figure out how to modify the criteria field in the DSUM formula to take an array created with statements in it.

Any ideas? All suggestions welcome, and thanks!

I've attached a worksheet as an example of my problem.

I have a large spreadsheet with many columns of data. I'm using the DSUM function to report on data based around 3 Schemes A, B, C.

Therefore I would like to know the Cost of each Scheme, please bear in mind not every record has a Scheme attached to it.

This I have managed to do using the DSUM function.

However I would also like to add criteria:

The cost of each Scheme per a financial year that is selected.

Hi,
Some time ago I had an application made for me that trawls certain betting website and saves the days info into daily spreadsheets.
I am now trying to convert this data into another spreadsheet that updates (adds to each field) from each days' sheet.
I have succesfully made a command search through each individual workbook to leave me with data that I now need to link.(using Dsum)
My problem is that there are around 4500 cells that need to be linked to a database that will add them all up to work a percentage. (although its the same cell numbers that dont change, only the spreadsheet changes.)
1. Is there a way I can make a button that will say export the days relevant cells to the linked workbook and,
2. How do I get the workbook 'collect' the info and add it rather than replace it?
thanks in advance.
Graydon

I have a large spreadsheet of data containing part numbers. The part numbers
are entered as numbers and then custom formatted to display dashes (i.e, part
number 1234567890 displays as 123-456-7890). I am trying to use a DSUM
formula that will add the beginning inventory (rows) for a month (column) for
a particular group of parts. For example, all parts that begin with "3002"
(i.e., 30020007409). I cannot for the life of me get the criteria to
recognize the part number. I have tried criteria such as ">30020000000", and
"3002*". Nothing is working. It's like Excel either isn't recognizing the
number OR it can't do DSUM based on more than one piece of criteria (which it
should be able to do). Help!

Hi All,

I've been using the DSUM function to pull in data from another worksheet.

In brief, the problem is that when I Cut & Paste the formula from C7 to C8, I want the Column reference to change but not the Row reference.

If I copy across the Columns then the result is OK – e.g. The Formula will change from…

#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!A$17:A$18)#

To…

#=DSUM([HBAP_SHUTDOWNS_2008.xls]NWM_SuccessfulShutdownsByDepartment!$A$4:$E$2924,"Total",'Calculations Page'!B$17:B$18)#

I have looked a using OFFSET(ADDRESS(ROW/COLUMN())) for the Criteria Part of the DSUM function, but without much success.

My ‘Criteria’ for the DSUM function is the Day. Which I have going across the rows in a worksheet used just for calculations. I tried to arrange my ‘Criteria’ Data in columns but because the Column must have a name, in this case ‘Day’ this didn’t work.

e.g.

Day
01/01/08
02/01/08
03/01/08

Also,

Date
01/01/08
Day
01/01/08

But I can’t change criteria to add 2 rows each time formula I copied!
I have researched a lot about this problem an it seem that this is functionality is missing from Excel – at least up until 2003 (My version).

I know this might sound a bit confused but this is the first time I’ve tried to articulate a problem.

Thanks for any suggestions / questions.

Ben

Hello,

The data range includes blanks. I cannot DSUM using a criteria that is blank. Is there a forumula I can use.

This excel spreadsheet explains it a bit better.

Thanks

Hi there,

I'm trying to use the dsum function, but am running into problems with the criteria. What I would like is for the function to add the footage (in column C)for each brand (column A), but also filter by brand category (column B).

So essentially, I'm trying to figure out the correct criteria to do this.

Here is what I have so far, but it returns the #NAME? error:

=DSUM(A5:C11,"Footage",((brand=MB AND Category=HB)))

Thanks for any help!

I have been trying to follow along with Chip Pearson's DSUM Tutorial and apply it to an application here at work.

It is located at http://www.cpearson.com/Newsletter/C...2007_10_08.htm

I am trying to sum the production totals for item codes between two dates. I eventually want the dates to be dynamicly set by a user input, but I cannot even get the DSUM to work using hard entered values. I have tried entering actual dates, the serial numbers for the dates and everything else I can think of.

I am enclosing a zip of a small sample of the worksheet, I have even used the same names Chip Pearson uses in his tutorial, just applying them to the ranges in this sample.

If anyone can tell me where I am going wrong, I would be most appreciative.

Hi
I use Office 2000 and have a very simple spreadsheet that is SUMing the figures in a colum. The cells are all formated to be currency with the Ł symbol and configured to two decimal places.

However the spreadsheet produces a figure of (for example) Ł488.15 and the real value should be Ł488.13

I know the error is only very small, but I can not move forward if I cannot get Excel to produce accurate figures. I guess the problem is in the way that Excel is rounding up, but how do I stop it ?

Should I be using DSUM for example?

Many thanks in anticipation,

Skelly

I have inherited a spreadsheet with SDUM functions, but I don’t understand
the notation used in the entry for the “field”, i.e. =DSUM(database, field,
criteria). Let me see if I can explain…
DB and FIRM are defined names referring to $B$6:$EB$182 and $DS$2:$DT$3
respectively. FIRM contains properly formatted criteria.
B254=1
C254=B254+1
…
T254=S254+1
and so on such that row 254 appears to be the column number (offset by 1 due
to starting in column B).

The formula in T190 is =DSUM(DB, +T254, FIRM).
The formula in U190 is =DSUM(DB, +U254, FIRM).
…
The formula in DQ190 is =DSUM(DB, +DQ254, FIRM).

For High Potential and Other Potential, they have T191=DSUM(DB, +T254, HPOT)
and T192=DSUM(DB, +T254, OTHPOT) where HPOT and OTHPOT are defined names
similar to FIRM.

What does the “+” in front of the cell reference do? I need to create
similar rows with different criteria, but I can’t figure out what impact the
“+” makes. When using the “+”, is using the $ in front of the 254 to keep it
from changing when coping to other rows also allowed?

Can I use the DSUM feature to look at a data sheet and return the sum of all
values which occurred in a given month? Example:

Fields - Date Premium etc.

I want to look at the date field and find for each month what the total
premium are.

I started with DSUM('2004'!$A$6:$AI$471, Premium, ???????)

Not sure how to word the rest. An "If" statement, maybe?

Any ideas would be helpful.

Thanks,
Ken Ivins

I want to add a reminder line to my excel workpage. I have a list of dates
that I have certain appointments, also I have a countdown of the number of
days until that appointment. All of this data is already in a table. What I
need it to do is to count the number of appointments I have in the next 7
days. When using the dget or the dsum, I get an error #VALUE

Database: SST (name of the range the database is in)
Field: 3
Criteria: <7

In the criteria line, it wants text, I dont understand why it wont take a
number when a number is what I am looking for.

Any help would be appreciated

I have been using data base in spread sheets for almost 25 years, Started
with Super Calc 2.

I am using an accounting spread sheet in excel.

I want to use another sheet that adds all the amounts of particular
accounting identifiers.

The identifies are 6 text characters, I.e.. 400-10 plus a string of
characters. i.e.. 400-10 Sales-Dayton-Manufacturing.
Well as you would guess sometimes the identifier is the long on and some
times it is only the left 6 characters

I add another column and fill it with the fist 6 characters of the account
identifier column using the left function. i.e.. left(f2,6) Then I use
that column for my Dsum statement. Sometimes it picks it up and sometimes
not.

So I add two more columns and want to just the left three characters so that
I pick up 400's for test purposes. I write if (f2=400,I2,' ') in another
column but it doesn't recognize 400 which I can plainly see. Since the left
function is a text function I suspect 400 is a text function but I think the
if function should still work.

Please help me!
charles
937 836 3272

I have a database that includes the following categories: Wages & Wages Benefits. When I use the following formula [=dsum(database,5,criteria), if the criteria includes the word Wages, I would expect the total to include only amounts for Wages, but I get the total for Wages & Wages Benefits?
Thanks in advance

I am trying to do a Dsum on multiple sheets I named my first table January so when I use the Dsum on the sheet Sales Summary work sheet then I have criteria on the sales summary for equal to P in shipping and Y In State the formula looks like this =DSUM(January[#ALL], January[Gross Sales],G16:H17) and it keeps coming up with #VALUE!. I have tried various methods to fix this to no avail. Any suggestions would help.

Hello. My assistant and I are creating some financial documents in Excel 2010. We set up a DSUM formula that links to a trial balance Excel file in another worksheet. At times, when we open the file using the DSUM formula, if the trail balance is not open, all the DSUM formulas become VALUE errors until we open up the trial balance file.

Is there a way to make it so these VALUE errors will not show up without having to always open the trial balance file? We tried to use the edit links and update the links, but that didn't work. I was hoping there is a way around this problem. Thanks for anyone that can help us!

Dave