Free Microsoft Excel 2013 Quick Reference

- How get value of status bar sum?
- DSUM not pulling the data - why
- Problem with DSUM (Excel XP)
- Problem with criteria for DSUM (Excel XP)
- DSUM with dynamic criteria?
- DSUM: Multiple Criteria
- Data analysis problems
- Conditional criteria in DSUM
- DSUM Function - Can't repeat column ref. down rows.
- DSUM - Criteria is a blank
- Database Sum using Multiple Criteria in Multiple Columns
- Dsum "#value!"
- SUM not producing accuracy to 2nd decimal
- "+" in DSUM field?
- DSUM for date range
- Dget or dsum, that is the question
- If statments using text
- DSum formula
- DSUM on multiple sheet
- DSUM Link to Other File Causing Value Error

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.

===============

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

(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

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

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 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.

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

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!

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

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

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!

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.

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

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?

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

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

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

Thanks in advance

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