Restrict counting orders after date x ONLY to customers that also ordered before date x

[Stijn@hccnet.nl]

Hi guys,

I once again need your help. I use a sales database with one order per row (including order date). Another tab holds a lists of dates.

What I want to do is to count the number of orders after each date in the second tab, but ONLY the orders from customers that also placed an order in a certain period running up to that same date.

I attached an example worksheet. Say, I want to know the number of orders AFTER Jan. 3 from those customers that also ordered BEFORE AND INCLUDING Jan. 3. (Customers A, B and C before and including Jan. 3; they placed 2 orders after Jan. 3).
Customer analysis EXAMPLE.xlsx

Thanks in advance,

Stijn

[DonkeyOte]

Not immediately obvious if you're looking for total count or unique count ?

Below is one approach

Please Login or Register  to view this content.

where Blad2!A2 holds date - eg 3rd January and Blad1 per sample file.

NOTE: Above formula is not efficient so be wary of using on large ranges and/or in large quantity.

[Stijn@hccnet.nl]

First off: works perfectly, thank you so much. Don't fully understand it, so in case you'd have the time to explain the elements, would love to know.

Regarding the efficiency, my file is quite large in fact so the calculations are taking some time, but I had completely filled it up with sumproducts anyway, so nothing new in that sense.

[Stijn@hccnet.nl]

Hmm, turns out it's actually too inefficient -- excel keeps freezing as I copy the formula to multiple cells. Any possibility of calculating it in a way less stressful on my pc (does not matter if it involves extra columns, worksheets or anything)?

Thank you in advance.

[DonkeyOte]

In light of prior post could you please provide us with a little more detail, i.e:

- How big is your source data range in reality ?
- How is your source data sorted ? (eg by date then customer or other)
- Could you sort your data differently to now if desired ?

- How many summary calculations are you performing (dates) ?

There are a number of more efficient approaches you could adopt but answers to the above could be significant in terms of determining "which" of these approaches is most suited to your dataset.

[Stijn@hccnet.nl]

Yeah ofcourse:

- I have a sales database consisting of 19.831 products sold, divided over more than 10.000 orders. As I ultimately want to count orders, I created a column that uses a COUNTIF array formula to designate unique orders based on order number (therefore I adjusted your original proposed formula to sum this unique order column).
- Right now the products sold are sorted primarily by customer value (= total amount spent), secondarily from old to new -- however, the file is set up so that sorting otherwise would not affect any of my calculations.
- The number of summary calculations is about 800, but the secondary worksheet holds only a couple dozen UNIQUE dates. Regardless, calculating for just one date already takes about 2 minutes.

[DonkeyOte]

I created a column that uses a COUNTIF array formula to designate unique orders based on order number (therefore I adjusted your original proposed formula to sum this unique order column).

Given above a further sample file which reflects your actual setup might be helpful here - small dataset only (replace confidential values of course).
If as implied you have 10-20k arrays on your source sheet this will have a significant impact on performance.

In the interim...

I'd suggest sorting your sales database by Customer and then Date (old to new).

I would then be inclined to add a formula adjacent to your source data which would in pseudo-terms work along the lines of:

Please Login or Register  to view this content.

where Col A holds customer and Col B date and C is the "helper" column.

What the above will do is define the first date a client placed an order and assign this same value for all records for the same client.

By sorting your data as outlined the above calculation is very (very) basic and thus very fast (no need for exact match etc).

With the above calculation in place you can then find number of orders placed after a given date where same customer has placed orders on or before that same date courtesy of a standard COUNTIFS formula:

Please Login or Register  to view this content.

where Blad2!A2 holds date - eg 3rd of January

The COUNTIFS approach will be quicker than SUMPRODUCT as it's non-iterative.

Note: following on from above if you do use SUMPRODUCT / Arrays be sure to keep precedent range sizes to a minimum / optimised at all times.

[Stijn@hccnet.nl]

Hi DonkeyOte,

Before I start: Happy holidays. Christmas dinners have prevented me from responding sooner.

I have to apologize, as I've been caught not providing you all relevant information. Your last proposal is very clear and fast, thank you for that. However, I made the grave mistake of initially summarizing my issue as counting orders after date X by customers from before date X. For this issue, the helper column works perfectly, but what I would actually want to know is orders after date X from customers that also ordered in the period 3-8 weeks before date X. I think the helper column won't do here, as the relevant order could be that customer's first order or his hundredth.

I attached a part of my file, personal data has been changed. In case this particular calculation can only be done with your orignal formula, so be it, but I seems as if my 2011 Intel i3 processor and 4 GB RAM are not going to cut it...
Sales database SAMPLE.xlsb

[DonkeyOte]

I made the grave mistake of initially summarizing my issue as counting orders after date X by customers from before date X. For this issue, the helper column works perfectly, but what I would actually want to know is orders after date X from customers that also ordered in the period 3-8 weeks before date X.

In reality the above removes the need of establishing the initial transaction and thus permits use of SUMIFS, COUNTIFS & AVERAGEIFS all of which will perform much better than an Array / SUMPRODUCT.
(above assumes [given file format] that you may use formulas new to XL2007)

Example:

Please Login or Register  to view this content.

In terms of rough performance indicator using just your sample file ... calculating col G (alone) whilst using SUM Array took approx 1.5 seconds whereas with SUMIFS that dropped to around 0.09 seconds.

SUMIFS is not super-fast when used in massive volumes and/or with huge ranges but it will still be a significant improvement than your Arrays.

If you're still having performance issues once modified let us know.

[Stijn@hccnet.nl]

Yeah much faster, thank you! But my issue remains with one column: Summary!J.

Right now, Summary!J3 holds the total number of orders since date Summary!F3, but what I need is the total number of orders since date Summary!F3 by those customers only that also ordered in that same 3 to 8 week period previous to date Summary!F3 relevant to the individual customer in Summary!D3 and Summary!E3. It is important to note here that the customers in Summary! are not all of the customers relevant to the calculation; Data! includes those in Summary!, but holds many more.

This I was/am able to calculate with an adjusted version of your very first formula (of quite many by now, for which I cannot thank you enough), but I am afraid I stil do not see how it can be done with a 'lighter' calculation.