Counting # of Clients if another Column is > 0

[soonerborn88]

I'm trying to design some sort of countif/countifs formula(Sheet 1, Column B) to count the number of clients involved in each job type...



-----Sheet 1-----(Column B needs to count the number of individual clients that hired different job types...For Example: Cell B2 should result with 2 because on sheet 2 both company A and company B had 'Professional' job types....whereas sheet1 cell B3 should result with 3 because on sheet 2 all 3 companies hired 'Office Services' job types)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]# of Clients[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Professional[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Office Services[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Light Industrial[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Skilled Trades[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Other[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












-----Sheet 2-----(This sheet contains a table showing the # of jobs each client company hired, specific to each job type. There are no duplicates.)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Professional[/TD]
[TD="align: center"]Office Services[/TD]
[TD="align: center"]Light Industrial[/TD]
[TD="align: center"]Skilled Trades[/TD]
[TD="align: center"]Other[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Company B[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Company C[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]













Above is a very simplified version of what i'm working with(in my actual file, I have over 9,600 unique clients and 30+ job types)

Any recommendations would be greatly appreciated!

 

[etaf]

try
=COUNTIF(INDEX(sheet1!$B$2:$F$100,0,MATCH(sheet2!$B1,sheet1!$B$1:$F$1,0)),">0")

https://www.dropbox.com/s/q377vr48pevs53t/LookupHeaderCountif.xlsx?dl=0

 

[soonerborn88]

Are the sheets right in your formula? Sheet 1 doesn't have anything in column F.

 

[etaf]

it was only a sample
but F has OTHER ?