Countif & Removing duplicates

T

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!
 
Sort by date Sort by votes
Anyone help with this?

The most recent post got it right for last month's report, but this month I've got an error.

One company has bought products in two areas, A & B, and this is what I'm getting:

Barns ltd | Yes | A | 1
Barns ltd | Yes | B | 2
Barns ltd | Yes | A | 3

I'm hoping for:

Barns ltd | Yes | A | 1
Barns ltd | Yes | B | 1
Barns ltd | Yes | A | 2

Any ideas?
 
Upvote 0
Maybe,

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 104px"><COL style="WIDTH: 109px"><COL style="WIDTH: 77px"><COL style="WIDTH: 103px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">New Business?</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Company Name</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Sales Area</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Helper1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Helper2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">NoBarns LtdA</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">NoBarns LtdB</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">YesBarns LtdA</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">YesBarns LtdB</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">YesBarns LtdA</TD><TD style="TEXT-ALIGN: right">2</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

Helper column formula

D2:- =A2&B2&C2
E2:- =SUMPRODUCT((D$2:D2=D2)*(A2="yes"))
 
Upvote 0
jasonb75 said:
Maybe,

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 104px"><COL style="WIDTH: 109px"><COL style="WIDTH: 77px"><COL style="WIDTH: 103px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">New Business?</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Company Name</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Sales Area</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Helper1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Helper2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">NoBarns LtdA</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">NoBarns LtdB</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">YesBarns LtdA</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">YesBarns LtdB</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yes</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Barns Ltd</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">YesBarns LtdA</TD><TD style="TEXT-ALIGN: right">2</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

Helper column formula

D2:- =A2&B2&C2
E2:- =SUMPRODUCT((D$2:D2=D2)*(A2="yes"))
Click to expand...
Cheers Jason! What calculation would you use to count the 1's though? I'ev got #N/A's all over the place at the moment :)
 
Upvote 0
Something like

=sumproduct((C:C="sales area to count")*(E:E=1))

Should do the trick.
 
Upvote 0
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Solved
Countifs with Multiple Criteria
Replies
3
Views
373
Kaper
Kaper
A
  • Question Question
Excel Function to Extract Text String, Remove Duplicate & Count
Replies
5
Views
415
arijitirf
A
S
  • Solved
Count the occurance of a duplicate in a column using structured references
Replies
3
Views
480
sspatriots
S
R
  • Solved
Excel/Google Sheets COUNTIFS formula to count rows, but with a substring in the Range.
2
Replies
11
Views
725
Fluff
Fluff
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
557
Kaper
Kaper

Share this page

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