sumproduct by criteria, month, & year

[Eric M.]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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"),--(D110="nameA"),C1:C10)

 

[Eric M.]

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\)]

Then I think Biff's (T. Valko's) formula will do what you want.

Rick