Summing unique values in a list based on TWO criteria?

[RockandGrohl]

=SUM(--(FREQUENCY(IF('Unique Clients'!D:D>(TODAY()-366),'Unique Clients'!A:A),'Unique Clients'!A:A)>0))

Hello hello everyone, hope the world is treating you as well as it can and you're all staying safe.

The formula above allows me to effectively count the number of unique customers in a list where their book date (column D) is within the last 365 days. The clientID's are in column A so for example, we can have any number of bookings and it's one line per booking, so clientID 169 can appear 4 times as they have made 4 bookings, but as their most recent bookings are within this last 365 days, they are counted as "1"

I need to expand this to one more criteria, so that I know how many in Column i also have >1

Can anyone please explain how this is possible? Thank you.

 

[Eric W]

Not tested, but it should be:

=SUM(--(FREQUENCY(IF('Unique Clients'!D:D>(TODAY()-366),IF('Unique Clients'!I:I>1,'Unique Clients'!A:A)),'Unique Clients'!A:A)>0))

I'd also recommend using actual row numbers:

=SUM(--(FREQUENCY(IF('Unique Clients'!D1:D10000>(TODAY()-366),IF('Unique Clients'!I1:I10000>1,'Unique Clients'!A1:A10000)),'Unique Clients'!A1:A10000)>0))

for wherever the end of your data is. It'll make the formula run much faster.

 

[RockandGrohl]

Nice one Eric that works really well.

It's led to a further development however, is it possible to add yet another criteria on? Say I want to check in Column E of "Unique Clients" for something in cell A8?

 

[Eric W]

Sure, you can add another condition the same way. Maybe:

=SUM(--(FREQUENCY(IF('Unique Clients'!D:D>(TODAY()-366),IF('Unique Clients'!I:I>1,IF('Unique Clients'!E:E=A8,'Unique Clients'!A:A))),'Unique Clients'!A:A)>0))

You should start to recognize the pattern now. If you want to see if column E contains the text from A8 (like "complete" is found in "job is complete"), then try:

=SUM(--(FREQUENCY(IF('Unique Clients'!D:D>(TODAY()-366),IF('Unique Clients'!I:I>1,IF(ISNUMBER(SEARCH(A8,'Unique Clients'!E:E)),'Unique Clients'!A:A))),'Unique Clients'!A:A)>0))