Criteria Based Monthly Counts

Request some help to customize the attached script to count the number of live “BUYER’s” during a months period.

I need the flexibility to select the Month and Year from the calendar controls and using the data set in Tab labeled “Data” determine this….

How many TOTAL buyers by Category (Column C) were alive during the selected Month?
How many UNIQUE buyers by Category (Column C) were alive during a specific Month?

Example if I want to know the number of BUYERs alive in March 2013 then:

Commencement date in Column D of the Data Tab should not be after March 31, 2013, but it can be anytime earlier than it, AND
Closure date in Column E of the Data Tab should not be before March 1, 2013, but it can be anytime on or after this date

Have manually extracted the data and created a tab labeled as March. You’ll notice that all these entries comply with the above logics. The counts are then populated against each category

Similar calculation need to be run on the month selection made in Calendar Control E

Columns F and G – Compares whether a BUYER who was alive in the Month and Year selected in Calendar Control C is also ALIVE in the comparison Month and Year selected through Calendar Control G or not, if yes it gives the counts.

Basically, I need to move the script from data specific calculations to count for the complete Month…..

Thanks in advance!

______________________________________________________________________________________________________________________________________________________________

What the script does currently?

On making a selection of a specific date from the Calendar controls from columns C, E and G. It gives me a count of the Total PURCHASERS after complying of the following 2 logics:

Periodic Counts – Column B

Total Buyers who had Commencement date on or before a certain date basis date in Column D of Data Tab
Total Buyers who had Closure date on or after a certain date basis date in Column E of Data Tab

Periodic Counts – Column C

Unique Buyers who had Commencement date on or before a certain date basis date in Column D of Data Tab
Unique Buyers who had Closure date on or after a certain date basis date in Column E of Data Tab

The above calculation are run for columns D and E as well.

Columns F and G – This segment compares whether a BUYER who was alive on the date selected in Calendar Control C is also ALIVE on the comparison date selected through Calendar Control G or not, if yes it gives the counts.

Hi simplyxl

Please post a workbook with results for each Column completed and illustrate how you got those results. Is this Code intended to be EITHER Select Month and Year OR Select Periodic Counts?

Hi simplyxl

What about this question

Is this Code intended to be EITHER Select Month and Year OR Select Periodic Counts?

Or is this an entirely NEW Workbook?

Hi simplyxl

I've a question regarding your "Illustration of the request.xlsx"...you have the same Date in Cells C3 and G3...is this correct?

Question.jpg

Hi simplyxl

It makes sense but it doesn't answer my question. I'll ask again...

You have existing Code that gives you results based on specific dates in Cells C3, E3 and G3. That Code appears to work as required. DO YOU WISH TO KEEP THAT CAPABILITY? Or are we REPLACING that Code to give you this capability

to select Month and Year through calendar control to get the by category Monthly Periodic Counts

Hi simplyxl

We're REPLACING the Code...Yes or No?

Thank you...I have Columns B, C, D and E resolved...have not yet resolved Columns F and G. Please check you Sample File "Illustration of the request.xlsx" to make certain YOUR results are accurate.

Hi simplyxl

Try the Code in the attached...it appears to do as you require...let me know of issues.