+ Reply to Thread
Results 1 to 18 of 18

Complex unique count formula

  1. #1
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    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
    Attached Files Attached Files
    Last edited by Eustace07; 02-04-2023 at 05:35 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    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. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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"))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    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
    Last edited by Eustace07; 02-02-2023 at 02:18 AM.

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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. #7
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    Re: Complex unique count formula

    I've amended the data and added a couple more splits payments. Thanks
    Attached Files Attached Files
    Last edited by Eustace07; 02-03-2023 at 06:02 AM.

  8. #8
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Complex unique count formula

    Your showing 2 payments for:
    Haxelnut, CAM67 and AG_13789
    But there's only 1 record that matches these values. Am I missing something?

  9. #9
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    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. #10
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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. #11
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    Re: Complex unique count formula

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

  12. #12
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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. #13
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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))
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    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. #15
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    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. #16
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    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. #17
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Complex unique count formula

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

  18. #18
    Registered User
    Join Date
    10-23-2014
    Location
    Fife
    MS-Off Ver
    M365
    Posts
    49

    Re: Complex unique count formula

    Many many thanks. This works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Complex UNIQUE formula based on multiple criteria
    By Mike-Marks in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-07-2022, 08:23 PM
  2. Replies: 6
    Last Post: 11-17-2015, 10:03 AM
  3. [SOLVED] Help with Complex COUNT formula
    By hack4u in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-31-2015, 01:55 PM
  4. [SOLVED] Challenging complex formula- unique count for multiple criteria on several levels
    By joannelittell in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-30-2013, 09:52 PM
  5. complex count formula?
    By Cheymeister in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-01-2013, 04:30 PM
  6. [SOLVED] Complex Count Unique, Filter and Pivot Macro
    By lemmussibericus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2013, 04:01 AM
  7. Formula Complex with IF and Count
    By Sultix in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-29-2008, 07:39 PM

Bookmarks

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