Calculating the number of people who have purchased more than once, over rolling periods

**mightybracket123** · 11-20-2023, 05:20 AM

Hi all,

I have sales data which has been pivoted to show the number of purchases people have made per month, over the last year.

I need to do a formula that will count the number of people who have purchased more than once, per 3 month period (i.e. we're looking at repeat purchasers).

I've attached an example of how the data looks and the expected outcomes (just for a couple of 3 monthly periods, rather than all of them).

I can't quite figure out how to do a formula that will combine a sum of each person's purchases, and then count the number of people in the data where their sum is greater than once.

Any help would be much appreciated !

**Glenn Kennedy** · 11-20-2023, 05:25 AM

One way:

=LET(A,B3:D10,B,BYROW(A,LAMBDA(x,COUNTIF(x,">0"))),ROWS(FILTER(B,B>1)))

**mightybracket123** · 11-20-2023, 05:36 AM

Hi Glenn,

Thanks for taking the time to look into this.

I noticed that when I drag the formula in the example across, when I get to cell D18, it's only returning a value of 2, rather than 3 (I've highlighted this in the attached).
In this example, the person made 2 purchases in May (rather than 1), so I'm not sure if this is throwing the formula off?

Apologies, when I put the example together, I just put in 1s quickly, but a person could make more than 1 purchase in a month.

**Glenn Kennedy** · 11-20-2023, 05:45 AM

I'm about to board a flight. Just check that in the other places it is selecting the correct range.

**Jacc** · 11-20-2023, 05:56 AM

Try this:

Formula:

Please Login or Register  to view this content.

**mightybracket123** · 11-20-2023, 06:09 AM

Hi Jacc,

That's perfect !!! Thanks so much ! Just to check, where you have the letter "t" in the formula, is that just a letter assigned to a parameter, rather than "t" meaning a particular function of it's own?

**Jacc** · 11-20-2023, 07:13 AM

It's just a random letter that is required in the Lambda function. Internally in the lambda function it represents a row.

**HansDouwe** · 11-20-2023, 07:29 AM

The formula written slightly differently:

Formula:

Please Login or Register  to view this content.

**mightybracket123** · 11-20-2023, 07:33 AM

Thank you so much everyone for all your help. I'd never have gotten the solution without your input, I really appreciate all the help

**Jacc** · 11-20-2023, 09:51 AM

Glenn, I see you often use REDUCE. Could it be used here? I tried and it didn't work but then again I never used it.

**HansDouwe** · 11-20-2023, 11:58 AM

Thanks for the feedback and rep

. Glad to have helped.

The formula can be written with the REDUCE statement, but that is not obvious or useful in this case.
This creates a contrived solution:

Formula:

Please Login or Register  to view this content.

**Jacc** · 11-20-2023, 04:50 PM

Thanks Hans, it was nice to get that confirmed! Pretty much what I tried to do but couldn't.

Yeah it looks a little awkward but it's not that bad.

**HansDouwe** · 11-21-2023, 01:23 AM

You are Welcome!

Thanks for the feedback and glad to have helped.

If you haven't already, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

Calculating the number of people who have purchased more than once, over rolling periods

LinkBack

Thread Tools

Rate This Thread

Display

Calculating the number of people who have purchased more than once, over rolling periods

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Re: Calculating the number of people who have purchased more than once, over rolling perio

Thread Information

Users Browsing this Thread

Similar Threads

Formula to show how much people owe each other for gifts purchased?

Help with calculating portfolio returns over rolling 30 yr periods

calculating number of three month periods between two dates...

Calculating Number of Days for job length periods

Calculating number of people

[SOLVED] Calculating number of periods when payment amount changes

Calculating number of periods when payment amount changes

Bookmarks

Bookmarks

Posting Permissions