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.



[TABLE="width: 513"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD]A1X001 - DERBY[/TD]
[TD]DERBY[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]A2E001 - GLASW[/TD]
[TD]GLASW[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 1,241[/TD]
[/TR]
[TR]
[TD]A4E001 - LEEDS[/TD]
[TD]LEEDS[/TD]
[TD] - [/TD]
[TD] 52[/TD]
[TD] 678[/TD]
[TD] 87[/TD]
[TD] 154[/TD]
[/TR]
[TR]
[TD]AAA001 - MILTK[/TD]
[TD]MILTK[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]AAE362 - DARTF[/TD]
[TD]DARTF[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 60[/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]AAE535 - LEEDS[/TD]
[TD]LEEDS[/TD]
[TD] 310[/TD]
[TD] 250[/TD]
[TD] - [/TD]
[TD] 249[/TD]
[TD] 231


[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 674"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Branch[/TD]
[TD]Branch Code[/TD]
[TD]Jun-15[/TD]
[TD]Jul-15[/TD]
[TD]Aug-15[/TD]
[TD]Sep-15[/TD]
[/TR]
[TR]
[TD]Trading Accounts Prioir 12 Months[/TD]
[TD]Peterborough[/TD]
[TD]PBORO[/TD]
[TD="align: right"]512[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trading Accounts Prioir 12 Months[/TD]
[TD]Glasgow[/TD]
[TD]GLASW[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trading Accounts Prioir 12 Months[/TD]
[TD]Dartford[/TD]
[TD]DARTF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trading Accounts Prioir 12 Months[/TD]
[TD]Leeds[/TD]
[TD]LEEDS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.