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.

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.

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.

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.

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.