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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

P
  • Question
Having trouble with forumla to use Countifs for only specific names listed in a column
Replies
1
Views
441
Paradigmgaming911
P
T
  • Solved
Adding Another Condition to 'Countifs' Formula to not count duplicates
Replies
3
Views
263
Fluff
Fluff
G
  • Solved
Removing duplicates rows
Replies
7
Views
156
Gcsingh90
G
JoshLyman
  • Solved
COUNTIF problem with table range
Replies
2
Views
216
JoshLyman
JoshLyman
Keebler
  • Question
Find lastest date in range
Replies
5
Views
81
Keebler
Keebler

Forum statistics

Threads
1,214,832
Messages
6,121,847
Members
449,051
Latest member
excelquestion515

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