+ Reply to Thread
Results 1 to 4 of 4

Countifs unique values only

  1. #1
    Registered User
    Join Date
    07-31-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    15

    Countifs unique values only

    Hey Guys,

    I have a rather long winded formula (which it might be difficult to keep up with), so i'll list the variables I require:

    Filter by account - cell C2
    Filter by product - cell C3
    Quarter from - cell C4
    Quarter to - cell C5
    Year - cell C6

    More data in an additional tab called "Database_sheet":
    Column J - reference to the quarter
    Column P - reference to the year
    Column O - reference to account
    Column L - reference to product

    So I have a formula which counts all the references (Column DY in Database_sheet) depending on the filters as mentioned above (Cell C2-C6 filter criteria).

    The only problem I have is that the same references are being doublecounted and there is no additional filter. Can someone help me count ONLY the unique values??

    My formula currently is:

    =IF(AND($C$3="TOTAL",$C$2="TOTAL"),COUNTIFS(Database_sheet!$DY:$DY,$B14,Database_sheet!$J:$J,">="&$C$4,Database_sheet!$J:$J,"<="&$C$5,Database_sheet!$P:$P,$C$6),IF(AND($C$3="TOTAL",$C$2<>"TOTAL"),COUNTIFS(Database_sheet!$DY:$DY,$B14,Database_sheet!$J:$J,">="&$C$4,Database_sheet!$J:$J,"<="&$C$5,Database_sheet!$O:$O,$C$2,Database_sheet!$P:$P,$C$6),IF(AND($C$3<>"TOTAL",$C$2="TOTAL"),COUNTIFS(Database_sheet!$DY:$DY,$B14,Database_sheet!$J:$J,">="&$C$4,Database_sheet!$J:$J,"<="&$C$5,Database_sheet!$L:$L,$C$3,Database_sheet!$P:$P,$C$6))))

    I appreciate this is long winded, sorry! Would be grateful for any help!!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Countifs unique values only

    Hi BCoke,

    It looks as though there is a sheet being referenced for the data validation dropdown menu's (Promo Summary v2.xlsx) which you've not included so it is not possible to see the content of your filters, can you upload the second sheet as well or include this in your main sheet.
    Thanks,
    HangMan

    You can say "Thank you!" by clicking Add Reputation below the post.
    Please, mark your thread [SOLVED] if you are happy with the solution.

  3. #3
    Registered User
    Join Date
    07-31-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    15

    Re: Countifs unique values only

    Hi Hangman,

    Apologies for that, I didn't realise they were still linked.

    You don't really need them, however. The customers are just the unique customers in the data tab. ie. Asda, Tesco etc. The packs are the individual SKUs (again in the data tab) and quarters are 1-4.

    As long as the formula references the cells in here, they should only be a criteria input so this should not change anything?

    Does this make sense, or is more required?

    Thanks!

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Countifs unique values only

    BCoke,

    Posting the same question in multiple threads is against our forum rules, rules you agreed to read and abide when you joined. Perhaps in haste you didn't actually read them. So click the FORUM RULES link in the menu bar above and take a moment to review them now. This will insure no unneeded infractions come your way.

    I have removed your duplicate threads, this time. No harm, no foul, as long as this doesn't recur. Thanks.

    -Jerry
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ 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] How to Count Unique Values with COUNTIFS?
    By Kingswood in forum Excel General
    Replies: 8
    Last Post: 08-01-2015, 08:02 PM
  2. How to return unique values using COUNTIFS ?
    By ccarmichael in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 06-04-2015, 03:36 PM
  3. Countifs for Unique Values
    By lucas813 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2015, 01:45 PM
  4. COUNTIFS unique values
    By Dan27 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-14-2014, 12:13 AM
  5. countifs for unique values
    By rbenguerel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-31-2014, 07:57 AM
  6. COUNTIFS with Unique Values
    By lefteegunzz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-23-2013, 04:25 PM
  7. Excel 2007 : Using countifs to count unique values
    By AlexZoom in forum Excel General
    Replies: 2
    Last Post: 09-23-2010, 09:41 AM

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