Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple DSUM criteria (MONTH & a numeric value) between worksheet
Hello. I'm trying to use DSUM to calculate a row of quantities that fit the
criteria of a specific month and size. Here's an image explaining what I am trying to achieve: http://i37.tinypic.com/ergxsx.jpg I have a DSUM formula included in the picture, which is not working. Any help would be greatly appreciated. Thank you, sarah |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple DSUM criteria (MONTH & a numeric value) between worksheet
First of all don't use the whole sheet as your table, use the real table
secondly when you need to use a formula in the criteria put no header in the criteria here's a simplification, assume you table including headers is A4:C10 assume the criteria is E1:E2 with E1 blank and E2 has this formula =AND(MONTH(A5)=4,B5=6) where A5 is the first cell in the date column with a date (header in A4) and B5 the first with a size, so that formula should return FALSE using your example the formula would be =DSUM(A4:C10,"Amount",E1:E2) returns 550 Of course the D functions are pretty old fashioned and you could easily use SUMIFS or SUMPRODUCT =SUMPRODUCT(--(MONTH(A5:A10)=4),--(B5:B10=6),C5:C10) -- Regards, Peo Sjoblom "sarah" wrote in message ... Hello. I'm trying to use DSUM to calculate a row of quantities that fit the criteria of a specific month and size. Here's an image explaining what I am trying to achieve: http://i37.tinypic.com/ergxsx.jpg I have a DSUM formula included in the picture, which is not working. Any help would be greatly appreciated. Thank you, sarah |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple DSUM criteria (MONTH & a numeric value) between worksheet
DSUM can't find MONTH)4), your Date criteria , in any of the actual dates in
the data. They must be the same kind of data. I got it to work this way: Remove the apostrophe from your DSUM formula, if there is one, so it is a live formula again. Insert a new column B on Sheet1. Giveit a heading of Month in B1 and this formula in B2: =Month(A2) Format B2 as a Number with no decimal places, then copy it down through all your rows of data. On Sheet2, change Date in your Criteria to Month in A9, and put the desired month number (4) in A10. Your formula should work now. You could also use a SUMPRODUCT formula like the following on Sheet2 to get the same result using your original data (don't have to add the Month column): =SUMPRODUCT(--(MONTH(Sheet1!A2:A10000)=$A$10),--(Sheet1!B2:B10000=$B$10),Sheet1!C2:C10000) Hope this helps, Hutch "sarah" wrote: Hello. I'm trying to use DSUM to calculate a row of quantities that fit the criteria of a specific month and size. Here's an image explaining what I am trying to achieve: http://i37.tinypic.com/ergxsx.jpg I have a DSUM formula included in the picture, which is not working. Any help would be greatly appreciated. Thank you, sarah |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple DSUM criteria (MONTH & a numeric value) between works
What if a new row / new data is introduced to the table? How can I ensure
that it will be picked up by my formula (without having to change the formula), if the whole sheet is not selected? Thank you for your reply! "Peo Sjoblom" wrote: First of all don't use the whole sheet as your table, use the real table |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple DSUM criteria (MONTH & a numeric value) between works
But would you add over a million? And how many columns would you use. If the
table is that big it is better to store it in Access and use a query in Excel. Regardless how big the table is you would need to use the method with a formula criteria I would probably use maybe A1:C10000 to start with, not one that is 1048576x16384 which is a huge size. -- Regards, Peo Sjoblom "sarah" wrote in message ... What if a new row / new data is introduced to the table? How can I ensure that it will be picked up by my formula (without having to change the formula), if the whole sheet is not selected? Thank you for your reply! "Peo Sjoblom" wrote: First of all don't use the whole sheet as your table, use the real table |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Multiple Criteria & Return Numeric Labels across single row | Excel Worksheet Functions | |||
SUM with multiple numeric and text criteria | Excel Discussion (Misc queries) | |||
Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions |