SUMPRODUCT unique Values

[Cet88]

Hi,

I have table as follows:
A Customer
B Location
C Turnover Jan
D Turnover Feb
E Turnover March
F Etc Etc for the next 48 months

I want to know how many customers in a specific location have had turnover in a 12 month period.
So if Col B = C3 (C3 on a different tab), and the value in any of the columns C-N is greater than zero then count it.

I started trying to use COUNTIFS with an OR but couldn't get this to work at all, then I moved on to a sumproduct but it is returning the total instances the branch appears as my 2nd criteria isn't correct.

=SUMPRODUCT((Workings!B:B=C3)*IF(SUM(Workings!C:N)>0,1,0))

I know the easy thing would be to add a total column to sum the turnover and do the countif off that, but this is for a rolling 48 months so that would add another 37 columns to the spreadsheet.

 

[Aladin Akyurek]

An Excel readable small sample would be great along with the desired results.

 

[Cet88]

HI,

A small section of the data.

A1X001 - DERBY	DERBY	-	-	-	-	-
A2E001 - GLASW	GLASW	-	-	-	-	1,241
A4E001 - LEEDS	LEEDS	-	52	678	87	154
AAA001 - MILTK	MILTK	-	-	-	-	-
AAE362 - DARTF	DARTF	-	-	60	-	-
AAE535 - LEEDS	LEEDS	310	250	-	249	231

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

As you can see some customer shave turnover in multiple months, but I am only concerned about a 12 month period and therefore need this to report as 1.
thanks

 

[Cet88]

Desired result is this format. count for each month how many customers for each location have traded with us in a 12 month period:

	Branch	Branch Code	Jun-15	Jul-15	Aug-15	Sep-15
Trading Accounts Prioir 12 Months	Peterborough	PBORO	512	1
Trading Accounts Prioir 12 Months	Glasgow	GLASW		1
Trading Accounts Prioir 12 Months	Dartford	DARTF
Trading Accounts Prioir 12 Months	Leeds	LEEDS

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Thanks for the samples. They do not seem to correlate. How does the desired result of 512 obtains for example given the input sample?

 

[Cet88]

Because it is only a snap shot of the data. IN the sample I have given I would want Leeds to only appear twice, darts once etc. As long as there is one instance of a sale in a 12 month period then it should be counted.