COUNTIF of Unique values with multiple criteria

[Jac3413]

I am trying to do a COUNTIF (and maybe that is not the correct function to use) of UNIQUE values based on multiple criteria (1) Location and (2) Level.

Multiple organizations report data based on the location and level of jobs. I need to show the "# of Unique Organizations Reporting" for any particular combination of locations & level...

For example, my data list looks like this

[TABLE="width: 240"]

<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Organization[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Level[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Junior[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Advanced[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Senior[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Advanced[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Intermediate[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Junior[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Company A[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Senior[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Company B[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Junior[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Company B[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Advanced[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Company B[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Intermediate[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]Company C[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Intermediate[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]Company C[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Advanced[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Company C[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Junior[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Company C[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Advanced[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Company C[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Senior[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]Company D[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Junior[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]Company D[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Senior[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]Company D[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Intermediate[/TD]
[/TR]
</tbody>
[/TABLE]


And I am trying to get a report that looks like this - looking for the formula I need for column C (however I have hundreds of rows of data with over 40 different locations, so I cannot manually count out to get the totals like I did for this example)


[TABLE="width: 365"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Level[/TD]
[TD="align: center"]# of Unique Org's Reporting[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Junior[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Intermediate[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Advanced[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]TX[/TD]
[TD="align: center"]Senior[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Junior[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Intermediate[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Advanced[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]Senior[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]




Help please! Thanks

 

[jbeaucaire]

This is the kind of thing a Pivot Table does in seconds...

Video: 2014-10-23_0809 - JerryBeaucaire's library