SUMPRODUCT help!

[tarantula]

I made a thread month ago to which I received great help.

My original question was:

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!

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))

Jason tweaked this slightly and we ended with the result:

Try this for helper column formula filled down as before

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

And for your result table formula.

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

This method will only count rows with Yes in column A

Now I've just found a fault although it is a small oversight and I'm sure easily remedied, I'm unable to work it out!

I've noticed that we have companies buying products in multiple sales areas, so I need to make sure I get a '1' for the first instance of each sale per area, at the moment I get:

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

But need:

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


Anybody shed any light?

 

[VaraK]

I think another helper column where we can concatenate the 3 columns.

=concatenate(A1,B1,C1). Fill this formula down to your data, Say this is in column E.

In column D or where you want the count, Do a countif on our newly created helper column.

=COUNTIF($E$2:E2,E2). You can fill this formula down to your data again. This will show the instance your data repeats.

Hope this helps.