Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct by criteria, month, & year
Column B = mmm/yy
Column C = Quanitity Column D = Criteria (nameA, nameB) I would like to sum column C for all quantities which match citeria in D (nameA) for a certain month and year (Dec, 2007). I tried unsuccessfully to use an IF statement for column D and a sumproduct for the remainder of the formula criteria. Any assistance would be much appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct by criteria, month, & year
Is Column B a real date formatted to look like mmm/yy or is it text
consisting of the 3-letter abbreviation for the month followed by a slash followed by the 2-digit year? Can you clarify what is in Column D? You have what appears to be 2 names separated by a comma/space, but your question only mentions the first listed name. Are you looking for a partial match (nameA in the cell even if there are other names) or a full cell match (nameA when only nameA is in the cell; no match if there is a second name coupled with it)? Rick "Eric M." <Eric wrote in message ... Column B = mmm/yy Column C = Quanitity Column D = Criteria (nameA, nameB) I would like to sum column C for all quantities which match citeria in D (nameA) for a certain month and year (Dec, 2007). I tried unsuccessfully to use an IF statement for column D and a sumproduct for the remainder of the formula criteria. Any assistance would be much appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct by criteria, month, & year
Column B = mmm/yy
Does column B contain true Excel dates but are formatted to display as above? Try this: =SUMPRODUCT(--(TEXT(B1:B10,"mmm/yy")="Dec/07"),--(D1:D10="nameA"),C1:C10) -- Biff Microsoft Excel MVP "Eric M." <Eric wrote in message ... Column B = mmm/yy Column C = Quanitity Column D = Criteria (nameA, nameB) I would like to sum column C for all quantities which match citeria in D (nameA) for a certain month and year (Dec, 2007). I tried unsuccessfully to use an IF statement for column D and a sumproduct for the remainder of the formula criteria. Any assistance would be much appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct by criteria, month, & year
I apologize for the confusion.
Column B is a real date that is formatted (12/1/2007). Column D has different names listed, but only one per cell. Thanks! "Rick Rothstein (MVP - VB)" wrote: Is Column B a real date formatted to look like mmm/yy or is it text consisting of the 3-letter abbreviation for the month followed by a slash followed by the 2-digit year? Can you clarify what is in Column D? You have what appears to be 2 names separated by a comma/space, but your question only mentions the first listed name. Are you looking for a partial match (nameA in the cell even if there are other names) or a full cell match (nameA when only nameA is in the cell; no match if there is a second name coupled with it)? Rick "Eric M." <Eric wrote in message ... Column B = mmm/yy Column C = Quanitity Column D = Criteria (nameA, nameB) I would like to sum column C for all quantities which match citeria in D (nameA) for a certain month and year (Dec, 2007). I tried unsuccessfully to use an IF statement for column D and a sumproduct for the remainder of the formula criteria. Any assistance would be much appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct by criteria, month, & year
Then I think Biff's (T. Valko's) formula will do what you want.
Rick "Eric M." wrote in message ... I apologize for the confusion. Column B is a real date that is formatted (12/1/2007). Column D has different names listed, but only one per cell. Thanks! "Rick Rothstein (MVP - VB)" wrote: Is Column B a real date formatted to look like mmm/yy or is it text consisting of the 3-letter abbreviation for the month followed by a slash followed by the 2-digit year? Can you clarify what is in Column D? You have what appears to be 2 names separated by a comma/space, but your question only mentions the first listed name. Are you looking for a partial match (nameA in the cell even if there are other names) or a full cell match (nameA when only nameA is in the cell; no match if there is a second name coupled with it)? Rick "Eric M." <Eric wrote in message ... Column B = mmm/yy Column C = Quanitity Column D = Criteria (nameA, nameB) I would like to sum column C for all quantities which match citeria in D (nameA) for a certain month and year (Dec, 2007). I tried unsuccessfully to use an IF statement for column D and a sumproduct for the remainder of the formula criteria. Any assistance would be much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year) | Excel Worksheet Functions | |||
SUMPRODUCT - Exclude LAST Row of Matched Criteria (Month & Year) | Excel Worksheet Functions | |||
sumproduct for month and year | Excel Discussion (Misc queries) | |||
how can I highlight a cell if it meets year and month criteria | Excel Worksheet Functions | |||
Counting distinct entries based on meeting month & year criteria | Excel Worksheet Functions |