Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date format problems | Charts and Charting in Excel | |||
average value from a table | Excel Discussion (Misc queries) | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions | |||
Date Problems | Charts and Charting in Excel |