Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Sum & Count Of Unique Numbers Meeting Criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 10:25.

  • #2
    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

    Comment


    • #3
      Re: Formula; finding unique numbers based on variables

      Yep, perfect job for a PivotTable

      Comment


      • #4
        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)))

        Comment


        • #5
          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
          Kris

          ExcelFox

          Comment


          • #6
            Re: Sum &amp; Count Of Unique Numbers Meeting Criteria

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

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X