YTLE#64: D Function: DAVERAGE DSUM & more

Hello All,

I have a sheet acting as a database (roughly 2500 records with roughly 30 fields), and on another page I have the data sorted into tables for plotting on various graphs. For various reasons I would prefer to avoid the use of Pivottables, and I beleive the Dsum, Daverage functions would be ideal for sorting the data.

One problem I have though is as follows:

I have a table of data with on one side one field (eg. month) and on the other another feild (eg. name) with the number of matching records for each combination displayed within the table.

---Jan, Feb, Mar, Apr, Etc.
Paul
Luke
Mark
John
Etc.

in each sell I could use a few different methods to find the number of mathches of both, eg.
=sumproduct("name_range"=name,"month_range"=month) but I would prefer to use the dcount function.

The problem with this is that the dcount funtion is =dcount(range,feild,criteria), where the criteria refers to a range of cells defining the criteria.

Eg. To do the count for paul in january, point to the range A1:B2 which would have the following values:

A1 = Month A2= Name
B1 = Jan B2 = Paul

Unfortunatly this means I would have to have a range for each dcount to look at to find the criteria, I would prefer to enter this straight into the formula (and for example have it pointing at the table column/row headings).

Is there any way of doing this?

Many thanks for any help provided!

Iain

How would you find the annual salary of female hourly workers in a database? I have used this formula:
=DAVERAGE(EmpData[Sex],"F",EmpData[Pay Type],"H",EmpData[Annual Salary]) amoung others. I have been working on this particular summary report for weeks. I have tried no less than 100+ formulas and all come back with you have used too many arguments, cannot have space, underscore, etc. Help!

Hello there

Currently I'm using excel 2003. Can anyone explain how the syntax works like for DAVERAGE using VBA?

Thanks in advance

Im trying to use the daverage function to average the cost of claims where total damages is between say £1001 and 5001. Im having difficulty setting up a suitable criteria range, as saying less than 5001 automatically includes all figures. Any ideas?

thanks

I have a Excel database with the grade and GPA of students. My fields are Student #, Grade, Race&Sex code with M and F entries and a field with their names. I have entered the formual =DAVERAGE(Students,GPA,E5:E6"M") and I can't get it to work. Can anyone give the right syntax? I want the GPA just for males and then for females. I was able to make it work when I wanted just Grade 12, but I can't get it for male and female. I think it has somehting to do with the text.
Thanks!!

I am having problems using the DSUM in my database worksheet. I keep getting VALUE instead the amount that I need to show.


Excel Jeanie HTMLInventory

A B C D E F G H 1 Vendor Item Date Category 1-Month
Order Category Total Order 2 All Foods Tea 8/30/2010 Food $ 500 Book #VALUE! 3 Epicurean Delights Jam 8/31/2010 Food $ 998 4 All Foods Coffee 9/1/2010 Food $ 900 5 Epicurean Delights Bottled water 9/2/2010 Food $ 2,800 6 Souvenir Trade Short sleeve t-shirts 9/3/2010 Clothing $ 2,500 7 Best T Long sleeve t-shirts 9/4/2010 Clothing $ 2,390 8 Readers Vermont travel guides 9/5/2010 Book $ 580 9 Best Sellers Vermont maps 9/6/2010 Book $ 400 10 Readers Paperback books 9/7/2010 Book $ 1,500 11 Best Sellers Vermont history books 9/8/2010 Book $ 1,050 12 Epicurean Delights Candy 9/9/2010 Food $ 800 13 Best T Baseball caps 9/10/2010 Clothing $ 1,900 14 Souvenir Trade Robes 9/11/2010 Clothing $ 2,100 15 Best Sellers Magazines 9/12/2010 Book $ 1,200 16 Readers Sudoku and Crosswords 9/13/2010 Book $ 800 17 Best Sellers Vermont cookbooks 9/14/2010 Book $ 1,000 18 Souvenir Trade Silk Ties 9/15/2010 Clothing $ 1,800 19 Best T Sweatshirts 9/16/2010 Clothing $ 2,100 20 Souvenir Trade Umbrellas 9/17/2010 Clothing $ 280 21 Souvenir Trade Postcards 9/18/2010 Book $ 900 22 Best T Towels 9/19/2010 Clothing $ 1,000 23 Souvenir Trade Earrings 9/20/2010 Clothing $ 1,500 24 Souvenir Trade Bracelets 9/21/2010 Clothing $ 1,300 25 Best Sellers Cards 9/22/2010 Book $ 1,250 26 Souvenir Trade Swim trunks 9/23/2010 Clothing $ 1,000 27 Best T Swimsuits 9/24/2010 Clothing $ 1,500 28 Allmart Toothpaste 9/25/2010 Personal $ 200 29 Allmart Toothbrushes 9/26/2010 Personal $ 250 30 Allmart Aspirin 9/27/2010 Personal $ 560 31 Allmart Shampoo 9/28/2010 Personal $ 800 32 Allmart Deodorant 9/29/2010 Personal $ 900 33 Allmart Comb 9/30/2010 Personal $ 1,100 34 Allmart manicure sets 10/1/2010 Personal $ 1,400
Spreadsheet Formulas Cell Formula H2 =DSUM(Table1[#All],Table1[Category],G1:H2)

Excel tables to the web >> Excel Jeanie HTML 4

Greetings,

I am trying to incorporate the use of the DSUM function to perform the following in a query.

Domain = "Actual Data"
Expression = "[WN Net Rate]" ' a field within the Actual Data table

My criteria is the issue and I am trying to sum the [WN Net Rate] filed in the Actual Data table where the [Date_act] field in the Actual Data table = a date field from a different table (less a month) in my query.... for example my attempt is as follows:

Code:

NetWNRate: DSum("[WN Net Rate]","[Actual Detail]","[Date_act] =" & DateAdd("m",-1,[Date_est]))

Since I have a relationship set up between two tables in my query I need to be able to return data from the Actual Data table where the date is equal to my "[Date_est]" value (less a month).

I receive an ERROR...

Any suggestions would be awesome...

I was given this task and can't get it to work properly.

Use the DSUM function in cell C14 to calculate the total gross sales of all January sales that satisfy the criteria in the rangeG16:H17. For the database, use January[#All], "Gross Sales". The formula will look similar to =DSUM(January[#All],"Gross Sales",G16:H17)

Any help you can give would be appreciated.

Thanks,

Morning,

I have a workbook that contains 5 wrkshts, 4 of the wrkshts contain values that I am need to get onto wrkshts1.

The 4 wrksht that have the value information I need, all have the same header labels on them on (column A1 thru G1).

I am trying to get the combine total sum for all 4 wrksht.

For the item count and $ amt, plus some other totals.

I cannot find the right syntax to get it to work.

Here is what I have used.

=DSUM('wrksht 2'!$A$1:$G$51101,'wrksht 2!'B1,A$22:A$23).
This returns the total item count for type A customers. But I need to get a combine total for all my type A customer from all 4 wrksht.

I tried =DSUM('wrksht 2'!$A$1:$G$51101,'wrksht 2'!B1,A$22:A$23)&(DSUM('wrksht 3'!$A$1:$G$51101,'wrksht 3'!B1,A$22:A$23)) but it did not return a good combine total.

It return a value of 2600028000 (Wrksht 2 total 26,000 Wrksht 3 total 28,000 ).
It just added the value of wrksht 3 to the end of wrksht2.


Hope some one can point me in the right direction.

Thank you