I have a list of publications and the academic departments of the coauthors for each publication. I'm trying to get a count of interdisciplinarity--the number of unique departments contributing to any single publication. I'm really struggling with this and I can't figure out what I'm doing wrong, it seems like it should be a relatively easy formula but the darn thing is stumping me.
Below is a sample table where the first two columns are my data and the "Interdisciplinarity" column is what I want the output of the formula to be.
The rule for counting interdisciplinarity would be something like "For each unique Publication Key, count the number of unique Departments).
I'm using Windows7 and Excel 2010
I've tried the following so far without luck:
=COUNTIFS([Publication Key],"="&[@[Publication Key]],[Department],"<>"&[@[Department]])
=SUMPRODUCT(--([Publication Key]=[@[Publication Key]]),--([Department]<>[@[Department]]))
<tbody>
</tbody>
Below is a sample table where the first two columns are my data and the "Interdisciplinarity" column is what I want the output of the formula to be.
The rule for counting interdisciplinarity would be something like "For each unique Publication Key, count the number of unique Departments).
I'm using Windows7 and Excel 2010
I've tried the following so far without luck:
=COUNTIFS([Publication Key],"="&[@[Publication Key]],[Department],"<>"&[@[Department]])
=SUMPRODUCT(--([Publication Key]=[@[Publication Key]]),--([Department]<>[@[Department]]))
Department | Publication Key | # of Authors | Interdisciplinarity |
01 | P1 | 1 | 1 |
02 | P2 | 1 | 1 |
03 | P3 | 2 | 2 |
04 | P3 | 2 | 2 |
05 | P4 | 3 | 1 |
05 | P4 | 3 | 1 |
05 | P4 | 3 | 1 |
06 | P5 | 3 | 2 |
06 | P5 | 3 | 2 |
07 | P5 | 3 | 2 |
<tbody>
</tbody>