Whoever can solve this is a genius..PLEASE HELP!!

In column "A", I have entry dates. In column "C", I have the names of wholesalers. Column "E" has the formula for what I am trying to achieve which is below.

=IFERROR(VLOOKUP(C2,Quota!B:O,LEFT(TEXT(A2,"m")+1),0)/(COUNTIF(C:C,C2)),0)

What this formula is doing is looking up the name of the wholesaler in a separate table and then pulling the corresponding quota number based on what month is in column "A". It then proceeds to divide that calculation by the amount of times each wholesaler name shows up in the sheet. This formula works perfectly, except for one detail: I need the counts in the second half of the formula to reset once a new month changes. Right now, what the formula is doing is if the wholesaler has entries in January and February, then January's quota number(the formula above) is going to divide by the amount of time the wholesaler shows up in January and February, rather than having the count reset each month like I want it to, I hope this makes sense. Any help or direction is GREATLY appreciated!

You could change the COUNTIF term to this:

SUMPRODUCT(($C$2:$C$1000=$C2)*(MONTH($A$2:$A$1000)=MONTH($A2))

Note that I've used a range from 2 to 1000, rather than a full-column range.

Hope this helps.

Pete

Thank you so much Pete!

2 things...

1- Any reason taking the entire column won't work,(rather than 1-1000? Id need to do all of column e since the sheet spread well beyond 999 lines. Any attempt to chnage the range you gave me, gives me an NA result.... Please don't give up on me!

2- Also, her is the formula I am now inputting, but I am getting a "need parentheses error). N ot sure why, but when i put it
=IFERROR(VLOOKUP(E2,Quota!B:O,LEFT(TEXT(A2,"m")+1),0)/SUMPRODUCT(($C$2:$C$1000=$C2)*(MONTH($A$2:$A$1000)=MONTH($A2))

If you use whole column references, it will create a matrix of over 1 million entries. That takes time to process and you will see a noticeable lag when calculating your worksheet. In earlier versions of Excel, you were not allowed to use full column references with SUMPRODUCT. Now you are but it is not recommended.

You would be better creating Dynamic Named Ranges for the data in columns A and C and referring to them instead of the full column references, or an arbitrary number of rows.

Thank you all, after some playng around, I got his formula to work for the second part of it f being wholesalaer and b being date month:

=VLOOKUP(F2,Quota!B:O,MONTH(A2)+1,0)/COUNTIFS(F:F,F2,B:B,B2)

Formula:

Please Login or Register  to view this content.