Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Sum & Count Of Unique Numbers Meeting Criteria

1. I agreed to these rules
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. 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

3. Re: Formula; finding unique numbers based on variables

Yep, perfect job for a PivotTable

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

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

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. I agreed to these rules
Join Date
9th April 2012
Posts
1

Re: Sum & Count Of Unique Numbers Meeting Criteria

Originally Posted by Krishnakumar
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

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

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