Cannot get SUMPRODUCT to count using date condition

[shstrating]

Dear community of helpers,

I am trying to get a tally of the number of calls made per Sales Rep after a given date.

Software: WinXP SP2 and Excel 2003 SP3

I have a worksheet called Calc on which I am doing several different calculations, pulling information from several other worksheets in the same workbook.

On the Calc sheet I have a column in which a Sales Rep's name appears in Firstname Lastname format (ColA=John Smith). I have another sheet called HH Calls in which the Sales Rep's name is split between two columns (ColH=John, ColI=Smith).

My first challenge was to get a formula on the Calc sheet that would tally the total number of calls made per Sales Rep by finding the Rep's name on the HH Calls sheet and counting the number of times it appeared. I accomplished that with the following formula:
=SUMPRODUCT(--(CONCATENATE('HH Calls'!$H$2:$H$7623," ",'HH Calls'!$I$2:$I$7623)=$A4))
I don't know if that's the greatest solution, but it seems to be giving me the correct numbers.

Now I'm trying to go back through the list of Sales Reps names on the 'HH Calls' sheet and only tally up the number of calls made after a given date. The dates for all the calls is in 'HH Calls' ColJ. The date against which I want to compare is in 'HH Calls' Cell $L$2. The idea is that we can alter the date in $L$2 in order to get a number of calls made since that chosen date.

I thought all I had to do was add another condition to the original SUMPRODUCT, this time trying to get the number of instances where a date listed in 'HH Calls' ColJ was greater than the date listed in Cell $L$2. This is what I tried:
=SUMPRODUCT(--(CONCATENATE('HH Calls'!$H$2:$H$7623," ",'HH Calls'!$I$2:$I$7623)=$A4),--('HH Calls'!$J2:$J7623>$L$2))

This does do a calculation, but the second half of the SUMPRODUCT is apparently only 'seeing' the date fields in ColJ that are blank. So for a Sales Rep that made a total of 32 calls, if 5 of those call records have a blank date field I get a result of 27 from my second SUMPRODUCT formula. If I populate those blank fields with a date then my tally goes back up to 32 regardless of whether that date is greater than, less than, or equal to the target date in Cell $L$2.

It's driving me nuts. I think I'm close to a solution, but maybe I can't do what I think I can do in terms of the comparison of an array of dates to a single date.

I tried using the Formula Evaluation dialog to decipher what is happening, but I just can't make heads or tails of where things are going awry.

I'm sorry I can't post a sample using Excel Jeanie HTML, but I can't access that website from this network and I can't install anything on this computer even if I get the file downloaded.

I'm hoping someone can show me the error of my ways.

Thanks, gang,
Steve

 

[Jonmo1]

You need to specify the sheet name on cell L2 that holds the date you're looking for.
Otherwise the formula looks at cell L2 of whatever sheet the formula is entered in.

=SUMPRODUCT(--(CONCATENATE('HH Calls'!$H$2:$H$7623," ",'HH Calls'!$I$2:$I$7623)=$A4),--('HH Calls'!$J2:$J7623>'HH Calls'!$L$2))

Hope that helps..

 

[shstrating]

jonmo1,

Oh My Gosh! How could I be such a dunderhead? I've been staring at this thing for an hour and couldn't see that. This is a perfect example of why it's almost impossible to check your own work. You get major 'blind spots'.

Well, if you could see me now you'd see my face is as red as a maraschino cherry.

Sorry to have bothered you with my ignorance, and thank you for being kind in your reply. I deserved a more terse response or at least a reprimand.

Thanks,
Steve