Free Microsoft Excel 2013 Quick Reference

Dsum with dates Results

I have a table with dates in one column and am trying to sum the values between dates inserted by the user.

Criteria is:

Brand Item qty TotalFullPrice NetEach TotalNetSale Date Type

In the date field, is it possible to enter a DSUM formula concatenating the start and end dates inserted by the user????

I had the same problem as Michel, and found your post most helpful. Where did
you find this information? I could not find it in Excel help, nor could I
find it in the five or six Excel manuals that I consulted.
Thank you.

"Aladin Akyurek" wrote:

> Let E1:E2 be the criteria range with:
>
> E1 housing Date as the relevant label.
>
> In E2 enter:
>
> =">"&J1
>
> where J1 houses a date that you want to invoke as the date criterion, which
> you can replace with any date at will..
>
> A DSum formula like:
>
> =DSUM(A1:B15,2,E1:E2)
>
> would calculate the desired sum.
>
> Is this what you wanted to do?
>
> "Michel from Belgium" in message ...
> > Hi everybody,
> >
> > I am working with Excel XP.
> >
> > I have a database with 2000 records and dbase formulas (mainly dsum) to
> > calculate
> > results in date ranges. The problem I have comes from the date criteria.
> As
> > I neeed to change regularely the dates, I tried to insert a formula in the
> > date criterion field, but without any success . The formula should look
> like
> > : > 8/1/2003 but I cannot make it work as > is not a numerical factor.
> >
> > Could somebody help me ?
> > Thanks in advance
> >
> > Michel from Belgium
> >
> >
>
>
>

I am using a spreadsheet to track & monitor our household expenses. On one sheet have columns for date (as 22/09/2010), category, income & expense. On the second sheet, I have a column for each category of income/expense, & rows for Jan, Feb, Mar, etc.

In the second sheet I have been using SUM & IF statements to calculate where the category in the first sheet is ‘Phone’, if it is greater than 01 Mar & if it is less than 31 Mar, then add together all corresponding values in the expense column. All these calculations make the spreadsheet wrok very slowly when I change something, & I was wondering if the DSUM formula would work for this? How do I get DSUM to use a range of dates (1 – 31 Mar) as a criteria?

An example of the formula I am currently using is: {=SUM(IF(Sheet1!B1:Sheet1!B5000=A14,IF(Sheet1!A1:Sheet1!A5000>=DATE(2010,3,1),IF(Sheet1!A1:Sheet1!A5 000<=DATE(2010,3,31),Sheet1!E1:Sheet1!E5000,""))))}

I have been creating a spreadsheet in Excel to track warranty sales for
my business. On this sheet i need to keep track of the Date, Associate
who sold it (by employee number), the ammount sold, Month to date
sales, and average dollar ammount.

Each day in the table consists of 2 columns. One with The date as the
title, and the other as "associate" as the title. In order to figure
out what the month to date, and average sales are i need excel tosearch
this table by an associate number (off of a seperate report sheet) then
have it adjust to add the totals in the column to the left, the date
column, which actually shows the sales amount. I have been trying with
multiple DSUM functions, and even with long sumif functions without
luck, does anyone know how i can accomplish this?

It is a rather long table, probably 40-50 columns. and set up similar
to this

7/21 associate 7/22 associate 7/23 associate
15.99 46465 4.99 484878 5.99 798765

22.99 46565 8.99 65686 4.99 65686

and ultimatly i need to be able to locate an associate (65686) and sum
his totals for the entire month (8.99+4.99) and show this on the report
page.

In my Database I have columns of data. Columns are Date, Type od data A or B, amount, Name. Rows are data running in to several thousands. Names repeat.

I need to total the amount column for each name under Type A & B in separate area of the spreadsheet.

Columns are: Date, Type of Data (A or B), Amount, Name

I need Total of Amounts for each Name in two separate columns:

Name Total Amount of type A Total Amount of Type B

Is it possible to use a non-contiguous range for the criteria parameter of the DSUM function?

I want to do something like this....
=DCOUNTA(wholesale_all,"Assignment Date",D2:D3,G2:G3)

This formulas won't work the way I have it typed, but is there a workaround to achieve the same result?

Hi, guyz.

Can anyone help me with a SUMIF or a DSUM formula (or any other type of formula) that will return the information required in the blue cells, using the AsOfDate (named range, B1) and the database in A3:C8.

I can't seem to be able to get a one-year date range based on the AsOfDate.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC11F11=
ABCDEF1As of date:12/31/2008 2 3Agmt. DateCrypts SoldPurchase Price 41/3/200515,000 5730/2006110,000 69/25/2007110,000 72/1/2008112,000 87/15/2008112,000 9 10 11 12 Mos. Ending12/31/2007 12 Mos. Ending12/31/200812 Crypts SoldPurchase Price Crypts SoldPurchase Price13Crypts Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I've got the DSUM function working properly, calculating the sum of sales based on a region and product type. However, I want to further refine my calculation by limiting the records included in the sum based on if the sale occurred during a specified range. Can this be done without:

1) Sumproduct (I've used this in the past with success, but the calcs take too long on my sheet

2) without making changes (i.e. adding formulas) to the master data table.

Basically I want to say, calculate total sales if:
- Region matches the one selected by the user AND
- Product matches the one selected by the user AND
- Sale date is >= beginning period and

IM TRYING TO GET THE SUM FROM COLUMN A "SALES " WHICH INCLUDE THE SALES
VALUE BY USING DSUM BASED ON SPECIFIC NAME FROM COLUMN B "NAMES " AND WITHIN
SPECIFIC DATE FORM COLUMN C"DATES" ALSO BASED ON SPECIE COUNTRY FROM COLUMN
D" COUNTRIES" HOW I CAN SET THE FORM
APPRECIATE YOUR HELP

MICHAEL

Hi,

I have three columns. They are Station, Date, and Rainfall. This
database will go from May 1 'till today's date with several stations
included. I want to add up all of the rainfall amounts from one station
from May 1 'till 14 days ago, 13 days ago, 12 days ago, and so on up to
today. I've been trying to use the DSUM function but I need to add in
the second set of criteria so it will only add the amounts up untill
however many days ago. Any suggestions??

Thanks,

Matt

--
matthoffman33
------------------------------------------------------------------------
matthoffman33's Profile: http://www.excelforum.com/member.php...o&userid=32148
View this thread: http://www.excelforum.com/showthread...hreadid=539436

I'm trying to DCOUNT with multiple criteria - but with one of the criteria changing.

I have a list with ..

Date - ID - Name - Data - Type

Where there are many "transactions" for each ID over a month.

I advance filter the ID's over to a new sheet - and I'm trying to get a new list

like...

ID - Combined Data

but only for a certain 'Type'

Basically I can do it with DSUM with multiple criteria...

ID - Type

but I have to change for every ID - or have a criteria listed for every ID which is more than annoying seeing the ID list changes depending on the month of Data I pull into it.

Sorry if this is confusing - I am probably thinking about this in the wrong way - and there is probably a simpler way of doing it.

In all the help for the database functions, the criteria always show the
values hard coded with a value like:

=">=10"

I want to have a sum of a certain column where the date is within 2 dates I
have derived (using formulas) in two cells outside the database and criteria
range.

For example, I have a database in A110 and the criteria is in F1:H2.
Column A is a column of numbers and column B is a column of dates. I have a
begin date calculated in J2 and an end date calculated in K2. Column F
matches column A and columns G and H both match column B so that I can do
multiple criteria on column B. How do I set the values of G2 and H2 so that
it basically says:

in G2 - Date in column B is >= J2
in H2 - Date in column B is also

Hi,

I have three columns. They are Station, Date, and Rainfall. This database will go from May 1 'till today's date with several stations included. I want to add up all of the rainfall amounts from one station from May 1 'till 14 days ago, 13 days ago, 12 days ago, and so on up to today. I've been trying to use the DSUM function but I need to add in the second set of criteria so it will only add the amounts up untill however many days ago. Any suggestions??

Thanks,

Matt

In all the help for the database functions, the criteria always show the
values hard coded with a value like:

=">=10"

I want to have a sum of a certain column where the date is within 2 dates I
have derived (using formulas) in two cells outside the database and criteria
range.

For example, I have a database in A1:D10 and the criteria is in F1:H2.
Column A is a column of numbers and column B is a column of dates. I have a
begin date calculated in J2 and an end date calculated in K2. Column F
matches column A and columns G and H both match column B so that I can do
multiple criteria on column B. How do I set the values of G2 and H2 so that
it basically says:

in G2 - Date in column B is >= J2
in H2 - Date in column B is also <= K2

I've tried in G2 - =">=J2"
I've tried in H2 - ="<=K2"

My DSUM looks like ("Score" is column A):
=DSUM(A1:D10,"Score",F1:H2) but I get the error:

A values in this formula is of the wrong data type

I looked at the help for that error but I don't see what I'm doing wrong.
It doesn't seem like Excel will allow a cell reference in the Criteria
section. All the help examples are hard coded. How do I get a criteria to
refer to a cell's value instead of hard coding the criteria values?

Thanks,
George

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

IM TRYING TO GET THE SUM FROM COLUMN A "SALES " WHICH INCLUDE THE SALES
VALUE BY USING DSUM BASED ON SPECIFIC NAME FROM COLUMN B "NAMES " AND WITHIN
SPECIFIC DATE FORM COLUMN C"DATES" ALSO BASED ON SPECIE COUNTRY FROM COLUMN
D" COUNTRIES" HOW I CAN SET THE FORM
APPRECIATE YOUR HELP

MICHAEL

Hello,

I have the following problem:

I used the DSUM formula to sum the costs between a range of dates (condition).
I have the dates in rows (records) and the costs for each date in columns(values).
Now I have changed the dates to columns (records) and the costs to rows (values).

The conditions remains the same >= Date1 and <=Date2.

What I would like to know if there is an alternative to DSUM that would look progressively at the rows and not at the columns for the Values and Rows.
Sort of I have the DSUM as a VLOOKUP and would need a DSUM for HLOOKUP.
I know they are different functions but it is just to give you the idea of what I need.

I would very much appreciate it if anyone could help me with this issue.

I've attached a simplified version of 2 sets of lists, one of which is missing the registrations.

For example, I need the total number of registrations for Coats with the channel ID's 2222 & 3333 only for 01/01/2012.

I want to be able to just enter additional Channel IDs in the boxes under the category names at the bottom and the registrations in column C are summed up as long as the date in column I matches column A.

I've tried DSUM which works perfectly but doesn't give me the total for a specific date.

Any help would be brilliant!

Having used DSUM successfully quite frequently, but am now trying to extract and sum data based on two date ranges as well as a third variable.

However, I'm having no success in getting this to work despite reading numerous threads and downloading/reviewing Dave's DFunctions With Validation example to make sure I wasn't missing something. (In the example file the DSUM formula to calculate "Full Cost" using date ranges also DOES NOT sum).

I'd appreciate if someone could take a look at the attached file and help clear this up.

A pivot table is a possible option, but I'm unsure (still reading/researching) how to filter the table to sum only data within desired date ranges (like you can with DSUM).

Thanks,
AAE

I have a Table called "NCR", In that table, in that table I have a material weight that is entered "MaterialWeight".

Totaled at the bottom of the report in the footer by:

Total Weight for the week: =Sum([MaterialWeight]) This gives me the total weight that is Nonconfoming for the week.

Out of that total I have different types of Nonconforming material input in a field called the "Disposition". In that "Disposition" column we enter one of the following:

SCRAP, RECLASS, USE AS IS or RETURN

I want this formula to total the "MaterialWeight" if my input for "Disposition" = SCRAP

=DSum("[MaterialWeight]","[Disposition]=SCRAP")

Whit this i get the output of #error

This doesnt work either
=DSum("[MaterialWeight]","NCR","[Disposition]=SCRAP")

With this i get no output what so ever

=DSum("[MaterialWeight]","NCR","[Disposition]='SCRAP'")

With this i get 17,654 which is the total in Table NCR, This works. Now I have to get it to just pull the MaterialWeight from the QUERY "NCR Weekly Query" not the entire report

=DSum("[MaterialWeight]","NCR Weekly Query","[Disposition]='SCRAP'")

With this i get #error, I need this formula to take into consideration the date range and the data pulled by the query.

Thanks in advance

I have a screen shot of the program with pbasic info to further understand what I am talking about.

http://i13.photobucket.com/albums/a2.../DSUMQUERY.jpg