Count the number of people in a table that meet a given target in consecutive months?

Hi experts
I need to use vba code to count the number of people that meet a target in consecutive months
example the target is 30 and above thus the total number of people that meet this target in consecutive months where the second month is 31/12/2010 is 2

Sam gets 50 in the first month and 45 in the second

peter gets 40 in the firts month and 40 in he second , thus 2 people meet the target in consecutive months.

The tabel itself cosnsist of over 10,000 records with over 2000 individuals is there a way to do this using vba?

Date Target Name
31/11/2010 50 Sam
31/11/2010 40 Dan
31/11/2010 40 Kent
31/11/2010 30 Peter
31/12/2010 45 Sam
31/12/2010 40 Kent
31/12/2010 25 Peter
31/01/2011 40 Chris
31/01/2011 10 Sam

positive side is that it automatically calculates changes, drawback is that you have to type in all distinct names by hand and drag-expand the formula. salesdata.xlsx

0

Thrawn3000Author Commented:

Thanks akoster
I have something similar to this working in my attached vba code, but given the size of the data
and number of names of consultants, please see tab wksCleandata I need to automate this in VBA
the tab wksDashboad shows where the sum of consultants who met a target for consecutive months should be, can this be simplified and automted so there uis no need to show distinct names.

You can do it with formulas. Note that the formula in cell E2 is different than the one in E3. Not a single formula but a row of formulas. The final count is the number of sales people that had two consecutive months of sales greater than a target threshold. If a salesperson had three or more consecutive months o multiple instances of two consecutive months it is still counted as one instance.

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

In cell G1 I entered the threshold sales amount. In cell G2 I entered the number of consecutive months. In cell G3 I entered this formula to count the number of salespeople satisfying the criteria:

=COUNTIF(D:D,TRUE)

The formulas assume the dates are in column A, the sales are in column B, and the names are in column C. Column D is used as the helper column (long formula above).

positive side is that it automatically calculates changes, drawback is that you have to type in all distinct names by hand and drag-expand the formula.

salesdata.xlsx