Countif & Removing duplicates

[tarantula]

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!

 

[tarantula]

Pivot table not be more in line with doing that?

Never used one actually, it's sounding like that's probably the one to go for.. and now to learn something new lol! Gonna be a nice afternoon

 

[jasonb75]

Another option would be to use a helper column.

For example

A | B | C | D
New Business? | Company Name | Sales Area | Helper

Assuming you have headers in A1:C1 and actual data starts in A2

In D2 enter = countif(B$2:B2,B2) then fill it down the column to the end of your data range.

Then in a new results table, anter the sales areas in the first column. (Z2 in formula below is cell with first sales area ref)

Your result formula would then be =SUMPRODUCT((A:A="Yes")*(D:D=1)*(C:C=Z2))

Notice that there is no reference to the company names in this formula, D:D=1 looks for the first instance of each name.

Although I would set the data ranges to match your used range as opposed to full columns, maybe dynamic named ranges would be of use.

 

[tarantula]

Jason I think you've got it there, my figures are looking correct!! I'm just checking them through now to make sure though before I start celebrating!

Thanks!!

 

[tarantula]

Right it's correct except with a couple of results..

@Jason

Great system, thanks. One problem I've found is that one customer, say Barns Ltd, is not showing in the Z2 results for new business. The count of how many times a company name is mentioned is not putting a '1' against the right line.. example:

A | B | C | D
New Business? | Company Name | Sales Area | Helper

No | Barns Ltd | D | 1
No | Barns Ltd | D | 2
Yes | Barns Ltd | D | 3
Yes | Barns Ltd | D | 4
Yes | Barns Ltd | D | 5

It's so close I can taste it

 

[tarantula]

Re: Countif & Removing Duplicates

Oh well some success for the day, thanks everyone for your input - appreciated!!

Back here again first thing tomorrow I think

 

[jasonb75]

Re: Countif & Removing Duplicates

Oops, I forgot to allow for that scenario.

Try this for helper column formula filled down as before

=SUMPRODUCT((A$2:A2="yes")*(B$2:B2=B2))

And for your result table formula.

=SUMPRODUCT((C:C=Z2)*(D:D=1))

This method will only count rows with Yes in column A

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: 109px"><COL style="WIDTH: 112px"><COL style="WIDTH: 77px"><COL style="WIDTH: 52px"></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></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">A </TD><TD>B </TD><TD>C </TD><TD>D</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">New Business? </TD><TD>Company Name </TD><TD>Sales Area </TD><TD>Helper</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD></TD><TD></TD><TD></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">No</TD><TD>Barns Ltd </TD><TD>D</TD><TD style="TEXT-ALIGN: right">0</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">No</TD><TD>Barns Ltd </TD><TD>D</TD><TD style="TEXT-ALIGN: right">0</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>Barns Ltd </TD><TD>D</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yes</TD><TD>Barns Ltd </TD><TD>D</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yes</TD><TD>Barns Ltd </TD><TD>D</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>

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

 

[tarantula]

Thanks Jason.

Still not quite there though. I'm getting:

Barns ltd | No | A | 0
Barns ltd | No | A | 0
Barns ltd | Yes | A | 1
Barns ltd | No | A | 1
Barns ltd | Yes | A | 2

Any ideas?

 

[tarantula]

I've tried a few variations on what you'd said and still getting no joy.. what a pain! Still, it is close!

 

[jasonb75]

Try this one in helper column, think it's what we've been looking for

=SUMPRODUCT((A$2:A2="yes")*(B$2:B2=B2)*(A2="yes"))

 

[tarantula]

Excellent!!! Thanks Jason you are a legend. Job done, working, great advice