I'd use:
=SUMPRODUCT(--(text($A$18:$A$3295,"yyyymm")=text($C4,"yyyymm")),
--($D$18:$D$3295=1000))

Where C4 contains a real date, too.

J.Mart wrote:

Hi,

I am trying to use a sumproduct function to return how many M&A deals were
over a billion dollars in a given year. I've been trying
=SUMPRODUCT(($A$18:$A$3295=$C4)*($D$18:$D$3295=10 00))
C4-represents a year. The sheet is set up as follows:
Output
No. of deals 1billion plus
2002
2003

DATA
Col.A Col.D
YEAR Value in $Millions

Second question: If I have a list of dates m/d/yy how can I write an
equation that will just return the month and year?

Thanks!

--

Dave Peterson