SUMPRODUCT with Date Condition returns #value

[TMCINNIS]

Okay, So I am in my over my head again. I have the formula below that is returning a #value error. The part in red is the new condition I added. I thought I had it right.

=SUMPRODUCT(('RAW DATA - PAGE CATEGORIES'!$A$2:$C$1048576=10/31/2014)*
('RAW DATA - PAGE CATEGORIES'!$C$2:$C$1048576="MICROSITEABC")*
(MMULT(0+ISNUMBER(SEARCH({"BILL","BILLING"},'RAW DATA - PAGE CATEGORIES'!$E$2:$E$1048576)),{1;1})>0),'RAW DATA - PAGE CATEGORIES'!$G$2:$G$1048576)

The original formula below is the one that worked fine before:

=SUMPRODUCT(('RAW DATA - PAGE CATEGORIES'!$C$2:$C$1048576="AUSTIN ENERGY")*(MMULT(0+ISNUMBER(SEARCH({"BILL","BILLING"},'RAW DATA - PAGE CATEGORIES'!$E$2:$E$1048576)),{1;1})>0),'RAW DATA - PAGE CATEGORIES'!$G$2:$G$1048576)

What I did was to add an additional date condition as a filter. This is when it broke. I am stretching my wings here and appreciate your help.

 

[FDibbins]

Try putting ther date in quotes "10/31/2014"
or use the DATE() function
(teh way you have it, it is trying to find etheanswerr of 10 div 31 div 2014 (0.000160169)

 

[TMCINNIS]

I did try the "" with the same results. I will try the Date() when I get to a computer. Other than that is the formula correct as written? Thanks.

 

[TMCINNIS]

FDibbins,
So, I was able to get it to work by pointing it to a cell with the date in it as compared to hard coding a date in the formula. I do have another question. How do I convert the =SUMPRODUCT formula to a formula using the same filtered conditions but gives me the average of all the items instead of the sum?

=SUMPRODUCT(('RAW DATA - PAGE CATEGORIES'!$A$2:$A$25000>=$B$295)*
('RAW DATA - PAGE CATEGORIES'!$E$2:$E$25000="AUSTIN ENERGY")*
(MMULT(0+ISNUMBER(SEARCH({"BILL","BILLING"},
'RAW DATA - PAGE CATEGORIES'!$J$2:$J$25000)),{1;1})>0),
'RAW DATA - PAGE CATEGORIES'!$K$2:$K$25000)