ExcelBanter - Sumproduct

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Sumproduct (https://www.excelbanter.com/excel-worksheet-functions/59747-sumproduct.html)

Why does this not work:

=SUMPRODUCT(--(CaseData!$X$2:$X$1000="Yes")*--(CaseData!$C$2:$C$1000=--DATE(2006,1,1)*--(CaseData!$C$2:$C$1000<=--DATE(2006,3,31))))

and this does:

=SUMPRODUCT((ISNUMBER(SEARCH("Yes",CaseData!$X$2:$ X$2000)))*(CaseData!$C$2:$C$2000=DATE(2006,1,1))* (CaseData!$C$2:$C$2000<=DATE(2006,3,31)))

I am trying to get the total number of Yes' in column X if the date is
between 1/1/6 and 3/31/6.

This works

=SUMPRODUCT(--(CaseData!$X$2:$X$1000="Yes"),--(CaseData!$C$2:$C$1000=DATE(2
006,1,1)),--(CaseData!$C$2:$C$1000<=DATE(2006,3,31)))

--

Regards,

Peo Sjoblom

"Jeremy Ellison" wrote in message
...
Why does this not work:

=SUMPRODUCT(--(CaseData!$X$2:$X$1000="Yes")*--(CaseData!$C$2:$C$1000=--DATE
(2006,1,1)*--(CaseData!$C$2:$C$1000<=--DATE(2006,3,31))))

and this does:

=SUMPRODUCT((ISNUMBER(SEARCH("Yes",CaseData!$X$2:$ X$2000)))*(CaseData!$C$2:$
C$2000=DATE(2006,1,1))*(CaseData!$C$2:$C$2000<=DA TE(2006,3,31)))

I am trying to get the total number of Yes' in column X if the date is
between 1/1/6 and 3/31/6.