0

So I am trying to come up with a ranking system that will start at 1, and increment for every different client we have, and reseting each month. Heres what I have:

=SUMPRODUCT(--([Client]=[@[Client]]), --([Receive Date]> [@[Receive Date]]) )+1

When this is entered, a #VALUE is returned to the cell, however, when I use 

=SUMPRODUCT(--([Client]=[@[Client]]), --([Sales Rep]> [@[Sales Rep]]) )+1

Or another column (that is not a date) everything seems to work out fine. Although, obviously, that makes the ranking on a rep by rep and client basis rather than month.

Any suggestions as to why the date column isn't working and how to fix it?

Edit: Oops, forgot to include this, since I want to reset the ranking every month, I have another column that concatenates the month and year, so a Receive Date of 3/10/2014 would become "3 2014".

Then I use the: 

=SUMPRODUCT(--([Client]=[@[Client]]), --([Concat Date]> [@[Concat Date]]) )+1

And thats where things get squirrelly.

Edit 2: Alright, so I think I figured out the formula, which I constructed in this dummy workbook. Everything works perfectly here! But when I copy and paste it into the actual workbook I'm using, the #VALUE error pops up. I've checked the formatting of each corresponding column from my test file to my real one. Has me totally stumped, thoughts?

J-Pwn
  • 3
  • 3

1 Answers1

0

Now that you added in the edit of Concat Date, it makes sense... My belief is that you probably are creating that field as text rather than a date and so your comparison operatir will no longer work.

So, suppose your sheet had your dates in column A beginning in A2 - You could create the Concat Date field using the following formula:

=DATE(YEAR(A2),MONTH(A2),1)

OR

=EOMONTH(A2,-1)+1

In effect, it will give you the first day of the month for any date BUT it will still be a date, so your comparison operator will still work.

PS - You can still use cell formatting to make it look like `3 20141 if you really wanted to, but the most important part is that it is a date value so you can use it for comparisons!!

Hope this does the trick!!

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • Just added another edit, I'll try the date function idea, and see if that changes anything in my working file. Thanks! – J-Pwn Mar 10 '14 at 18:41
  • Just tried the End of Month option, and it turned everything into #NUM.... not sure where to go from here. Are there any limits to the length of the arrays I'm referring to? I'm at about 1800 rows right now – J-Pwn Mar 10 '14 at 18:47
  • This has to be a matter of your date inputs... You have to ensure, as Barry Houdini mentioned, that you don't have any errors in ANY CELLS that are inputs to your sumproduct... All I can tell you is that the formula should work... From this point it has to do with the inputs.... – John Bustos Mar 10 '14 at 19:53
  • Ahhhh, there was ONE error in all of the 1800 rows! Deleted that cell and everything worked like a charm! Thanks a bunch guys! Never seen something where one cell could impact everything else in the arrays – J-Pwn Mar 10 '14 at 20:42
  • I figured that would have been the case. Glad you got it working!!!! Also, since you're new to SO, remember to mark the answer as your accepted solution. – John Bustos Mar 10 '14 at 21:36