Excluding duplications in rows only when using countif function

[OfficeMonkey666]

Hi,
I will try to explain this the best that I can.
I have a dump of data every month that I have to use to create reports. This dump has columns up to NG and rows are are currently at 1112, so its a large spreadsheet. The columns will not change but the rows will get longer per month. There are various reports that run so I dont want to change the structure of any or the source data.

The report I have to run is to find out how many companies work in specific sectors, each company has its own row and within this row certain columns hold the data of which sectors the company works in.

The columns are not defined in sectors, they are defined in products in which the company will enter a product and then select a sector against this product.
The issue I am having is that a company could make 6 products that would then be used in 3 sectors so in the source data there there will be 6 columns of products and 6 columns of secors but some of these sectors will be duplicated.
If I use a countif function arcoss the rows, it will count all times the sector is mentioned which isnt what I want.

Is there a formula that will count all the sectors but remove the duplications when they are duplicated in a row only?
I will try and put an example below:

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b[/TD]
[TD]c
[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD]h
[/TD]
[TD]i
[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]Product 1
[/TD]
[TD]product sector 1
[/TD]
[TD]match
[/TD]
[TD]product 2
[/TD]
[TD]product sector 2
[/TD]
[TD]match
[/TD]
[TD]product 3
[/TD]
[TD]product sector 3
[/TD]
[TD]match
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Company a
[/TD]
[TD]brakes
[/TD]
[TD]Cars[/TD]
[TD]No
[/TD]
[TD]Brakes
[/TD]
[TD]Vans
[/TD]
[TD]no
[/TD]
[TD]wheels
[/TD]
[TD]Vans
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Company b
[/TD]
[TD]wheels
[/TD]
[TD]cars
[/TD]
[TD]yes
[/TD]
[TD]wheels[/TD]
[TD]bikes
[/TD]
[TD]no[/TD]
[TD]brakes
[/TD]
[TD]cars
[/TD]
[TD]no
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]company c
[/TD]
[TD]wheels
[/TD]
[TD]vans
[/TD]
[TD]yes
[/TD]
[TD]brakes
[/TD]
[TD]vans
[/TD]
[TD]yes
[/TD]
[TD]brakes[/TD]
[TD]cars
[/TD]
[TD]yes
[/TD]
[/TR]
</tbody>[/TABLE]

This is a made up version of the report as I couldnt upload but I need a formula that will make the result be:
Cars = 3
vans = 2
bikes = 1

I have no need to use the product or match columns but if im using a formula like countif I can enter the name of the sector to find.
This data is found in the middle of the spreadsheet


Sorry if this is confusing!!!

Thanks

 

[admiral100]

Hi,

try this Cntl+Shift+Enter NOT just Enter

for "cars" =SUM(IF(FREQUENCY(IF(B2:J4="cars",ROW(A2:A4)-ROW(A1)+1),ROW(A2:A4)-ROW(A1)+1),1))

change "cars" as needed

 

[OfficeMonkey666]

Thank you for this, I will have to give it a try when I have access

as the sheets are big and the number of rows will change monthly, instead of doing B2:J4 and Row (A2:A4) could I just use the column and row letters so it looks in all of the text in the columns and rows?
like this?

for "cars" =SUM(IF(FREQUENCY(IF(GS:IG="cars",ROW(A:A)-ROW(A1)+1),ROW(A:A)-ROW(A1)+1),1))

as the data i need to use is in columns GS through to IG?

Thanks