# Complex unique count formula

1. ## Complex unique count formula

I wrote a formula below to count unique rows using nine non-adjacent criteria ranges from the source data. The unique rows are based on the values in column-G in the source data, that is, if there are duplicate items in the columnG the formula just looks at one. Criteria ranges PmtTypeRng and DebtAgeRng have a set of multiple items/criteria.

The formula sadly return null. Not sure what i'm doing wrong, will appreciate any insight into this or alternative way to write it.

=SUM(IF(FREQUENCY(IF((DateMonthRng=\$C\$4)(ZoneRng=\$E\$4)(ClientRng=\$C2)(ClientAccRng=\$D2)(DistroRng=\$E2)(PaymtDelbyRng="Customer")(PmtTypeRng={"DirectDebit","Cash","CreditCard"})(DebtAgeRng={"5-day","10-day","20-day"}))(PaymtEffect_Rng="Debit"),(MATCH(G2,G:G,0)),MATCH(G2,G:G,0))>0,1,0))

Thanks

2. ## Re: Complex unique count formula

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

3. ## Re: Complex unique count formula

Wise words there Glenn. I've added a sample excel sheet and the expected result is in the Monthly Summary tab. Many thanks.

4. ## Re: Complex unique count formula

So this should give you the first 3 columns. How do you know if the # of LatePayments is ever greater than 1 vs. a split payment?

=UNIQUE(FILTER(HSTACK(ClientRng,ClientAccRng,DistroRng),(MONTH(C4)=MONTH(DateMonthRng))*(E4=ZoneRng),"none"))

5. ## Re: Complex unique count formula

Hi Gregb11, many thanks for the help. You can see split payments when there are duplicate references in column-G in the transaction data. The splits could be x2, x3,x5, or more.

Thanks

6. ## Re: Complex unique count formula

Please update your sample data so it shows all the scenarios that need to be addressed. Right now in your expected results, the of # of payments are all 1. Please create data that will show more than 1 late payment, but also include split payments for the same acct.

7. ## Re: Complex unique count formula

I've amended the data and added a couple more splits payments. Thanks

8. ## Re: Complex unique count formula

Haxelnut, CAM67 and AG_13789
But there's only 1 record that matches these values. Am I missing something?

9. ## Re: Complex unique count formula

There are 2 distinct payments for Haxelnut

Mar-2022 09/03/2022 Haxelnut ltd SOC WEF980 1000 3200 CreditCard 5-day KU0087 14/03/2022 CAM67 AG_13789 USD 1000 3200 Card expired Customer 14/03/2022 Debit SOUTH1
Mar-2022 07/03/2022 Haxelnut ltd SOC WEFY589 2300 69000 CreditCard 20-day KU0087 27/03/2022 CAM67 AG_01 USD 2300 69000 Card expired Customer 27/03/2022 Debit SOUTH1

10. ## Re: Complex unique count formula

Yes, but these each have a different DistAgency, so wouldn't the line that has DistAgency of AG_13789 have 1 and the one with AG_01 have 1?

11. ## Re: Complex unique count formula

Correct! Sorry my mistake. The DistAgency of AG_01 should be same for both. Data amended.

12. ## Re: Complex unique count formula

OK, I'm going to continue with the assumption that this is also a mistake, but let me know when you get a chance. In your latest file, you show
Decagon Ltd CAM63 AG_1987 2

But I think the 2 should be 3. If 3 is not the right answer, please let me know.

13. ## Re: Complex unique count formula

I couldn't get it as nice as I wanted to unfortunately, and I'm sure there's a way I'm just not getting it right now. Maybe someone else will reply with a nicer solution, BUT, this gives you all the correct answers (as best I can tell).

So in cell B7, use this same formula from before:
=UNIQUE(FILTER(HSTACK(ClientRng,ClientAccRng,DistroRng),(MONTH(C4)=MONTH(DateMonthRng))*(E4=ZoneRng),"none"))

BTW, I added a named range for G5:G20 called "Ref". So in E7, you could use this formula, and copy down as far as you need to (you can copy down farther than the list shown in B7 in case with your real data, as it expands, the numbers will show up).

E7:
=LET(a,UNIQUE(SORT(FILTER(HSTACK(ClientRng,Ref,ClientAccRng,DistroRng),(MONTH(\$C\$4)=MONTH(DateMonthRng))*(\$E\$4=ZoneRng),"none"),1,1)),
b,SUMPRODUCT((B7=CHOOSECOLS(a,1))*(C7=CHOOSECOLS(a,3))*(D7=CHOOSECOLS(a,4))),
IF(b=0,"",b))

14. ## Re: Complex unique count formula

This is awesome!! Apologies for not making the data cleaner to deal with, I'm still getting used to it myself! Yes, you're correct, Decagon Ltd CAM63 AG_1987 should be 3. This is sparklingly amazing and it's gonna shed a ton load stress off me! Thank you very much Gregb11.

15. ## Re: Complex unique count formula

You're welcome - just wish I could have done it all with one formula. Oh well, glad it works for you and thanks for the rep!

16. ## Re: Complex unique count formula

Please Is there an alternative function to HSTACK to combine the ranges? Just noticed HSTACK is not in my work Excel Lookup & Reference directory. Quite strange as its Office 365 for Enterprise.

17. ## Re: Complex unique count formula

Try this:
=UNIQUE(FILTER(CHOOSE({1,2,3},ClientRng,ClientAccRng,DistroRng),(MONTH(C4)=MONTH(DateMonthRng))*(E4=ZoneRng),"none"))

18. ## Re: Complex unique count formula

Many many thanks. This works.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1