Free Microsoft Excel 2013 Quick Reference

- DSUM With Date Range
- Dsum with date criteria
- Using DSUM with date range
- DSUM with dates??
- DSUM with variable Criteria
- Dsum With Non-contiguous Range Criteria
- SUMIF or DSUM for date range
- Need help with DSUM and date ranges
- HOW TO USE DSUM WITH MORE THAN ONE CRITERIA
- Using DSUM with several criteria
- Using Dynamic Criteria with DCOUNT/DSUM
- DSUM with Criteria containing non-hardcoded values?
- Using DSUM with several criteria
- DSUM with Criteria containing non-hardcoded values?
- DSUM for date range
- How to use dsum with more than one criteria
- DSUM with columns as records and rows as values
- DSUM to include specific date
- Sum Data Between 2 Dates & Addtional Criteria
- Dsum Formula

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

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

> >

> >

>

>

>

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,""))))}

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.

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

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?

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.

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

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

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

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

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

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

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

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

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.

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!

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

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

No luck finding an answer? You could always try Google.