excel - SumProduct Not Working with Date Values -

so trying come ranking system start @ 1, , increment every different client have, , reseting each month. heres have:

=sumproduct(--([client]=[@[client]]), --([receive date]> [@[receive date]]) )+1 

when entered, #value returned cell, however, when use

=sumproduct(--([client]=[@[client]]), --([sales rep]> [@[sales rep]]) )+1 

or column (that not date) seems work out fine. although, obviously, makes ranking on rep rep , client basis rather month.

any suggestions why date column isn't working , how fix it?

edit: oops, forgot include this, since want reset ranking every month, have column concatenates month , year, receive date of 3/10/2014 become "3 2014".

then use the:

=sumproduct(--([client]=[@[client]]), --([concat date]> [@[concat date]]) )+1 

and thats things squirrelly.

edit 2: alright, think figured out formula, constructed in dummy workbook. works here! when copy , paste actual workbook i'm using, #value error pops up. i've checked formatting of each corresponding column test file real one. has me totally stumped, thoughts?

now added in edit of concat date, makes sense... belief creating field text rather date , comparison operatir no longer work.

so, suppose sheet had dates in column beginning in a2 - create concat date field using following formula:

=date(year(a2),month(a2),1) 

or

=eomonth(a2,-1)+1 

in effect, give first day of month date still date, comparison operator still work.

ps - can still use cell formatting make `3 20141 if wanted to, important part is date value can use comparisons!!

hope trick!!