Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Countif & Removing duplicates

This is a discussion on Countif & Removing duplicates within the Excel Questions forums, part of the Question Forums category; Hi board, new member here. I've been working on a calculation now for hours and am stuck - please help!! ...

  1. #1
    New Member
    Join Date
    May 2010
    Location
    England
    Posts
    17

    Angry Countif & Removing duplicates

    Hi board, new member here.

    I've been working on a calculation now for hours and am stuck - please help!! :D

    I have three columns of data I need to analyse, and report that in another column.

    Column headers are:
    A | B | C
    New Business? | Company Name | Sales Area

    With data as such:

    Yes | Acme Ltd | D

    The yes can also be a no, company name obviously a range of names, and sales area a-m.

    The result I'm going for is to have a COUNT of new business sales, per area. So looking, for example, for all the New Business: YES's, in Sales Area: D.

    Now to complicate that some business names (and the sales area and new business y/n) are going to be the same, as it's recorded against which products they've bought so there could be several rows for each business name.

    I need to remove the duplicate business names from the results.

    This is the best calculation I have got this far!

    =COUNT(1/FREQUENCY(IF(A2:A100="Yes",IF(c2:c100="D",)))

    I know that's not correct, but basically, count of criteria matching Yes and D excluding duplicate entries!

    Any help would be MOST appreciated!

  2. #2
    New Member
    Join Date
    Dec 2009
    Location
    Bangalore
    Posts
    30

    Default Re: Countif & Removing duplicates

    I think I understood your problem. If this is Excel 2007, Countifs function should do the needful. For earlier versions of Excel, create one column which is concatenation of New Business column and the area colum and run a countif for a string "yes" and area name. e.g. ikf the area name is "A" run a countif for "yes"&"A"

  3. #3
    New Member
    Join Date
    May 2010
    Location
    England
    Posts
    17

    Default Re: Countif & Removing duplicates

    Quote Originally Posted by pawan View Post
    I think I understood your problem. If this is Excel 2007, Countifs function should do the needful. For earlier versions of Excel, create one column which is concatenation of New Business column and the area colum and run a countif for a string "yes" and area name. e.g. ikf the area name is "A" run a countif for "yes"&"A"
    Thanks for your reply.

    Sorry it's Excel 2003.

    I've got that far using (say column D is the combined business and area column): =COUNTIF(D2:D100,"YESA") but the problem is the duplications by company name, as I may have 5 rows all showing from "Acme Ltd", but I need to count this as a single result rather than 5 in the count.

  4. #4
    New Member
    Join Date
    Dec 2009
    Location
    Bangalore
    Posts
    30

    Default Re: Countif & Removing duplicates

    there is an option to filter unique values in the advanced filter. Use that to "preprocess" your data and then run the logic i mentioned in the earlier post.

  5. #5
    Board Regular scottylad2's Avatar
    Join Date
    Feb 2009
    Location
    East Lothian.....near Edinburgh
    Posts
    1,913

    Default Re: Countif & Removing duplicates

    Sheet6

     NOP
    132Company 1yes17
    133Company 2no 
    134Company 3no 
    135Company 4yes 
    136Company 5yes 
    137Company 1yes 
    138Company 7yes 
    139Company 8no 
    140Company 1no 
    141Company 10no 
    142Company 11no 
    143Company 12no 
    144Company 11no 
    145Company 14no 
    146Company 15yes 
    147Company 16yes 
    148Company 1yes 
    149Company 18no 
    150Company 19no 
    151Company 20yes 
    152Company 21no 

    Spreadsheet Formulas
    CellFormula
    P132=SUMPRODUCT(1/COUNTIF(N132:N152,N132:N152))


    Excel tables to the web >> Excel Jeanie HTML 4

    This will show you the number of unique companies, still trying to work out the yes part though
    Deek
    Windows 7 home 64-Bit Edition
    Office 2007 Professional
    Sometimes posting from my I-phone......Appologies for the Typos in avdunce

  6. #6
    New Member
    Join Date
    May 2010
    Location
    England
    Posts
    17

    Default Re: Countif & Removing duplicates

    Quote Originally Posted by pawan View Post
    there is an option to filter unique values in the advanced filter. Use that to "preprocess" your data and then run the logic i mentioned in the earlier post.
    Thanks but unfortunately it's not reflected in the count which is crucial. The count column will be in a summary section of my workbook, to go out to various members of staff.

  7. #7
    New Member
    Join Date
    May 2010
    Location
    England
    Posts
    17

    Default Re: Countif & Removing duplicates

    Quote Originally Posted by scottylad2 View Post
    This will show you the number of unique companies, still trying to work out the yes part though


    Do you think that's maybe the way to go then, sort into unique company names then sort by yes and area code? Hmm..

    And I thought I was good with Excel

  8. #8
    New Member
    Join Date
    Dec 2009
    Location
    Bangalore
    Posts
    30

    Default Re: Countif & Removing duplicates

    It should work. you have to run the logic on the preprocessed data i.e. on a table created with unique records.

  9. #9
    New Member
    Join Date
    May 2010
    Location
    England
    Posts
    17

    Default Re: Countif & Removing duplicates

    The problem with that is this is a very small part of the overall sheet analysis that is going on so the filters will be coming on and off constantly, and this particular thing I want in a sheet summary which is at the header of the sheet..
    Last edited by tarantula; May 6th, 2010 at 09:24 AM.

  10. #10
    Board Regular scottylad2's Avatar
    Join Date
    Feb 2009
    Location
    East Lothian.....near Edinburgh
    Posts
    1,913

    Default Re: Countif & Removing duplicates

    Pivot table not be more in line with doing that?
    Deek
    Windows 7 home 64-Bit Edition
    Office 2007 Professional
    Sometimes posting from my I-phone......Appologies for the Typos in avdunce

Page 1 of 3 123 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com