# 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. ## Countif & Removing duplicates

Hi board, new member here.

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

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. ## 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. ## Re: Countif & Removing duplicates

Originally Posted by pawan
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"

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. ## 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. ## Re: Countif & Removing duplicates

Sheet6

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

 Cell Formula 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

6. ## Re: Countif & Removing duplicates

Originally Posted by pawan
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. ## Re: Countif & Removing duplicates

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

10. ## Re: Countif & Removing duplicates

Pivot table not be more in line with doing that?

Page 1 of 3 123 Last