#1   Report Post  
Posted to microsoft.public.excel.misc
duncan79
 
Posts: n/a
Default DSUM Date problems


I cant work out what expression i would use to make a DSUM function
count only Figures from certain months.... what expression would i use
in the criteria areas with a date header???? (a pivot table doesnt
really suite my needs.)


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=543320

  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default DSUM Date problems

Please post a sample of your data: most likely a SUMPRODUCT with unary
operators will do the trick for you. This link
http://groups.google.com/group/micro...t+unary&qt_g=1
will give a number of examples.

  #3   Report Post  
Posted to microsoft.public.excel.misc
duncan79
 
Posts: n/a
Default DSUM Date problems


my data looks like this



Date Invoice No.Company Property Amount

02.05.06 1116 Banana 3 Banana street 1 13
02.05.06 1117 Hamster 57 Hamster avenue 1 14
02.05.06 1118 Dave 16 Dave Close 15
02.05.06 1119 apple 177 Apple terrace 16
02.05.06 1121 apple 12 Apple Road 17

where i am summing the amounts and the totals need to be split into
company and month i have been able to use DSUM fine to calculate a
further running total with a paid : yes/no column and i want to do the
same for dates

have tried wildcards *.05.06 in the criteria, and a few other things
any way to make it work ???


--
duncan79
------------------------------------------------------------------------
duncan79's Profile: http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=543320

  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default DSUM Date problems

With your headers and sample data entered in cells A1:E6, the following
formula resulted in $33:
=SUMPRODUCT(--(A2:A6="02.05.06"),--(C2:C6="apple"),E2:E6)

This formula adds the elements in the Amount column when the date is
02.05.06 and the company name is apple.

If column F was a Paid? Yes/No column then this formula does the same
thing for Paid = yes:
=SUMPRODUCT(--(A2:A6="02.05.06"),--(C2:C6="apple"),--(F2:F6="yes"),E2:E6)

Some notes and caveats:
~Where you see a text string in the formula, such as "02.05.06",
"apple", "yes", you can use a cell reference.
~Spelling is critical: "apple" doesn't equal "aple" or "apple "

  #5   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default DSUM Date problems

Here's what to do, assume the table is called MyTable and the header for the
column you want to sum is called Amounts, and the header for the dates is
called Dates, if you use E1:F2 as criteria it would look like


Dates Dates
="=01/01/2006" ="<=02/28/2006"

then the formula

=DSUM(MyTable,"Amounts",E1:F2)

will sum for Jan & Feb of 2006



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"duncan79" wrote in
message ...

I cant work out what expression i would use to make a DSUM function
count only Figures from certain months.... what expression would i use
in the criteria areas with a date header???? (a pivot table doesnt
really suite my needs.)


--
duncan79
------------------------------------------------------------------------
duncan79's Profile:
http://www.excelforum.com/member.php...o&userid=30833
View this thread: http://www.excelforum.com/showthread...hreadid=543320



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date format problems Monty Charts and Charting in Excel 1 April 20th 06 05:17 PM
average value from a table TUNGANA KURMA RAJU Excel Discussion (Misc queries) 15 March 21st 06 07:52 AM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
Recurring annual events using a specific date as a trigger date Bamboozled Excel Worksheet Functions 1 June 6th 05 01:44 PM
Date Problems MikePiehl Charts and Charting in Excel 2 June 2nd 05 05:45 PM


All times are GMT +1. The time now is 12:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"