+ Reply to Thread
Results 1 to 7 of 7

COUNTIFS Function Problem

  1. 05-13-2014, 09:42 AM #1
    JC_VILLA789
    JC_VILLA789 is offline
    Registered User
    Join Date
    10-02-2007
    Posts
    32

    COUNTIFS Function Problem

    On the attached spreadsheet, I want to count the amount of times each product appears, discounting the times where it appears against the same name.

    For example, Product 2 appears against the name Michael Thompson three times, but I only want to count it once.

    Is there away to only count each product when it appears against a unique name?

    Only caveat is I do not want to have to type that person's name into the formula... Any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by JC_VILLA789; 05-13-2014 at 03:15 PM. Reason: SOLVED
    Reply With QuoteRegister To Reply
  2. 05-13-2014, 10:12 AM #2
    jackgan
    jackgan is offline
    Registered User
    Join Date
    07-31-2012
    Location
    Buffalo, United States
    MS-Off Ver
    MS Office 2007, 2010, 2013
    Posts
    59

    Re: COUNTIFS Function Problem

    Can you clarify for example what is the end result you want to see from the formula?
    Reply With QuoteRegister To Reply
  3. 05-13-2014, 10:13 AM #3
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: COUNTIFS Function Problem

    Using your posted workbook
    D1: a product to match....Product 1
    This regular formula returns the count of unique Product Line/Targets combinations where the Product Line equals D1
    Please Login or Register  to view this content.
    In the above example, "Product 1" has 4 unique combinations.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  4. 05-13-2014, 10:16 AM #4
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: COUNTIFS Function Problem

    We use a helper & hidden column with this formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    These to get the unique names.
    Please Login or Register  to view this content.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    and this to get your results.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  5. 05-13-2014, 10:37 AM #5
    ARGK
    ARGK is offline
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: COUNTIFS Function Problem

    Hi
    Please see attached. Hopefully this is what you are after.
    Tony
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 05-13-2014, 02:53 PM #6
    JC_VILLA789
    JC_VILLA789 is offline
    Registered User
    Join Date
    10-02-2007
    Posts
    32

    Re: COUNTIFS Function Problem

    Thanks all. Having a bit of trouble understanding the formulas given - even though they yielded the right results! Will have to spend a bit of time trying to digest it all
    Reply With QuoteRegister To Reply
  7. 05-13-2014, 03:13 PM #7
    JC_VILLA789
    JC_VILLA789 is offline
    Registered User
    Join Date
    10-02-2007
    Posts
    32

    Re: COUNTIFS Function Problem

    Scratch that... got it working, and I think I understand it! Thanks again.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Countifs problem - Using the "OR" function or someting similar
    By shepherdc2814 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2014, 05:58 PM
  2. [SOLVED] VBA: CountIfs function problem
    By Bezzie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2013, 08:09 AM
  3. [SOLVED] Problem calling WorksheetFunction.CountIfs() function
    By nfuids in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-04-2012, 10:35 AM
  4. Excel 2007 : More efficient: 2 long COUNTIFS or 1 COUNTIFS and 1 VLOOKUP based on the COUNTIFS?
    By dip11 in forum Excel General
    Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  5. COUNTIFS problem
    By javajoemorgan in forum Excel General
    Replies: 8
    Last Post: 10-15-2009, 11:28 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1