Countif & Removing duplicates

tarantula

New Member
Joined
May 6, 2010
Messages
17
Hi board, new member here.

I've been working on a calculation now for hours and am stuck :mad: - 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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Excel Workbook
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
Sheet6


This will show you the number of unique companies, still trying to work out the yes part though:LOL:
 
Upvote 0
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.
 
Upvote 0
This will show you the number of unique companies, still trying to work out the yes part though:LOL:
:)

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 :eek:
 
Upvote 0
It should work. you have to run the logic on the preprocessed data i.e. on a table created with unique records.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top