Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Sum & Count Of Unique Numbers Meeting Criteria

  1. #1
    Join Date
    28th January 2009
    Posts
    1

    Sum & Count Of Unique Numbers Meeting Criteria

    Hi

    I have a list, 50000 rows long with phone numbers and the service provider it belongs to. I want to extract some of this information.

    I'm looking for a formula which can give me the following:

    How many unique phone numbers is there in column B from service provider number 4 and 5?

    The following pic is just an example of how the list is compiled.


    How can I get sum up only the numbers from 4 and 5 and not the whole bunch?

    If I use this formula


    Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
     
     =SUM(N(FREQUENCY(D1:D51112,D1:D51112)>0))  



    I sum up all the unique phone numbers, but I don't know how to set a "if service provider is 4 or 5" if you get what I mean.

    This was the first part.

    The second part is finding out how many times each unique number from service provider 4 and 5 is listed.

    I greatly appreciate any help I get from you guys.

    Thanks
    Last edited by Dave Hawley; January 29th, 2009 at 10:25.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,229

    Re: Formula; finding unique numbers based on variables

    How many unique phone numbers is there in column B from service provider number 4 and 5?
    Use Advanced Filter with criteria and choose either Filter in place or copy to another location. make sure to select the "Unique values" option in the dialog box.

    How can I get sum up only the numbers from 4 and 5 and not the whole bunch?
    Options:
    1. Use the DSUM function with criteria
    2. Pivot Table (best choice)
    3. Formula using SUMIF function

    The second part is finding out how many times each unique number from service provider 4 and 5 is listed.
    Options:
    1. Pivot Table
    2. DCOUNT function with criteria
    3. Formula using COUNTIF function

    Per forum rules, threads are limited to ONE question. Start a new thread for each question. Please adhere to the rules.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

  4. #4
    Join Date
    23rd April 2007
    Posts
    3,886

    Re: Sum & Count Of Unique Numbers Meeting Criteria

    This will return the number of numbers from provider4 or provider5, not counting duplicates
    =SUMPRODUCT(1/COUNTIF(A:A,A1:A100),--((B1:B100=4)+(B1:B100=5)))

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Sum & Count Of Unique Numbers Meeting Criteria

    Hi,

    For a unique count..

    =SUM(IF(FREQUENCY(IF(C1:C30>=4,IF(C1:C30<=5,MATCH(B1:B30&C1:C30,B1:B30&C1:C30,0))),ROW(B1:B30)-ROW(B1)+1)>0,1))

    Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

    HTH

  6. #6
    Join Date
    9th April 2012
    Posts
    1

    Re: Sum & Count Of Unique Numbers Meeting Criteria

    Quote Originally Posted by Krishnakumar View Post
    Hi,

    For a unique count..

    =SUM(IF(FREQUENCY(IF(C1:C30>=4,IF(C1:C30<=5,MATCH(B1:B30&C1:C30,B1:B30&C1:C30,0))),ROW(B1:B30)-ROW(B1)+1)>0,1))

    Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

    HTH
    Hi HTH, Thanks a lot, your formula really helpfull.... and works well
    regards...

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Count Of Unique Numbers Based On A Criteria
    By swunk in forum Excel General
    Replies: 5
    Last Post: February 17th, 2008, 17:20
  2. Sum Numbers Meeting Criteria
    By swx1600 in forum Excel General
    Replies: 4
    Last Post: May 27th, 2007, 17:50
  3. Count Numbers Meeting Condition
    By cavemonkey in forum Excel General
    Replies: 6
    Last Post: May 24th, 2007, 17:42
  4. Count Numbers Meeting Criteria
    By pierrewhy in forum Excel General
    Replies: 5
    Last Post: November 14th, 2006, 01:40
  5. Count Numbers Meeting a Criteria/Condition
    By davekim1000 in forum Excel General
    Replies: 5
    Last Post: October 20th, 2006, 15:45

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
  •  
porno