+ Reply to Thread
Results 1 to 4 of 4

Count number of lines based on text and number criteria, without duplicates

  1. #1
    Registered User
    Join Date
    08-28-2017
    Location
    Portugal
    MS-Off Ver
    Microsof Office Professional Plus 2013
    Posts
    3

    Question Count number of lines based on text and number criteria, without duplicates

    Hello! New user here!
    I've been trying to figure out for a day and can't seem to find any help on any thread for this, so I decided to post a new one.
    Attached is the sample database for my problem. The final goal is to have a cell that returns the number of times a unique brand (Column A with no duplicates) is in Store (column B = YES) and with values below 0 (Column C <0).
    So in this case, the cell should return 3!



    Capturar.PNG

  2. #2
    Registered User
    Join Date
    08-28-2017
    Location
    Portugal
    MS-Off Ver
    Microsof Office Professional Plus 2013
    Posts
    3

    Re: Count number of lines based on text and number criteria, without duplicates

    Array Formulas
    Cell Formula
    E2 {=SUM(IF(FREQUENCY(IF(A2:A17<>"",IF(B2:B17="yes",IF(C2:C17<0,MATCH(A2:A17,A2:A17,0)))),ROW(A2:A17)-ROW(A2)+1),1))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Count number of lines based on text and number criteria, without duplicates

    Or:

    =SUM(IF((A2:A17<>"")*(B2:B17="yes")*(C2:C17<0),1/COUNTIFS(A2:A17,A2:A17,B2:B17,"yes",C2:C17,"<0")))

    C+S+E

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Count number of lines based on text and number criteria, without duplicates

    Hi HV. Try this ARRAY FORMULA:
    Please Login or Register  to view this content.
    Last edited by leelnich; 08-28-2017 at 07:43 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

+ 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] Count number of text without duplicates and blank
    By Lan Tran in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-26-2015, 07:49 AM
  2. [SOLVED] count total number of lines in big text files and export results for each txt to sheet
    By jtammyg in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-07-2014, 05:37 AM
  3. Count the number of rows/lines in a wrap text cell
    By ashleyhkim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-19-2013, 05:23 PM
  4. Count Number Of lines in text files and append values to beginning of file
    By motoxeryz125 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-27-2011, 11:14 PM
  5. count the number of duplicates and print the number of occurences
    By ash81sen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2010, 01:21 PM
  6. Count Number of Lines in Text File
    By NicB. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-05-2006, 08:37 AM
  7. [SOLVED] Count number of lines of text in a range/value.
    By Jacob in forum Excel General
    Replies: 1
    Last Post: 10-18-2005, 04:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1