Having difficulty counting rows when using multiple conditions

Mihos

New Member
Joined
Jun 23, 2014
Messages
7
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]]))


DepartmentPublication Key# of AuthorsInterdisciplinarity
01
P111
02P211
03
P322
04P322
05P431
05P431
05P431
06P532
06P532
07P532

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">{=SUM(<font color="Blue">--(<font color="Red">IF(<font color="Green">$B$2:$B$11=$B2,MATCH(<font color="Purple">$A$2:$A$11,$A$2:$A$11,0</font>)</font>)=(<font color="Green">ROW(<font color="Purple">$B$2:$B$11</font>)-ROW(<font color="Purple">$B$2</font>)+1</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;color: #FFFFFF;background-color: #262626;;">Department</td><td style="font-weight: bold;color: #FFFFFF;background-color: #262626;;">Publication Key</td><td style="font-weight: bold;color: #FFFFFF;background-color: #262626;;"># of Authors</td><td style="font-weight: bold;color: #FFFFFF;background-color: #262626;;">Interdisciplinarity</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">P1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style=";">P2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style=";">P3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style=";">P3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style=";">P4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5</td><td style=";">P4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">5</td><td style=";">P4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">6</td><td style=";">P5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">6</td><td style=";">P5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">7</td><td style=";">P5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Mihos

New Member
Joined
Jun 23, 2014
Messages
7
Thanks, VBA Geek! I'm still seeing some issues though. Interdisciplinarity still isn't calculating correctly and I'm getting some 0 values as well.

I'm definitely using {=SUM(--(IF($B$2:$B$16=$B2,MATCH($A$2:$A$16,$A$2:$A$16,0))=(ROW($B$2:$B$16)-ROW($B$2)+1)))} with ctrl+alt+enter to get the curly brackets.

Here's an example with actual data. "Interdisciplinarity (calculation result)" shows the value I'm getting with the above calc and "Interdisciplinarity (what should be)" shows what I should be getting:

DepartmentPublication Key# of AuthorsInterdisciplinarity (calculation result)Interdisciplinarity (what should be)
251099154496111
301999154501111
233999154502111
280000154509111
233999154515101
300999154747211
300999154747211
280000154878212
235999154878212
300999154956401
300999154956401
300999154956401
300999154956401
301999155000202
300999155000202

<tbody>
</tbody>
 
Last edited:

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
you are right, my previous formula was not done properly

here is a solution which will work


<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">FREQUENCY(<font color="Green">IF(<font color="Purple">$B2=$B$2:$B$16,MATCH(<font color="Teal">$A$2:$A$16,$A$2:$A$16,0</font>)</font>),ROW(<font color="Purple">$B$2:$B$16</font>)-ROW(<font color="Purple">$B$2</font>)+1</font>),1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />




<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;;">Department</td><td style="color: #333333;;">Publication Key</td><td style="color: #333333;;"># of Authors</td><td style="color: #333333;;">Interdisciplinarity (calculation result)</td><td style="color: #333333;;">Interdisciplinarity (what should be)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;;">251099</td><td style="text-align: right;color: #333333;;">154496</td><td style="text-align: right;color: #333333;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;;">301999</td><td style="text-align: right;color: #333333;;">154501</td><td style="text-align: right;color: #333333;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;;">233999</td><td style="text-align: right;color: #333333;;">154502</td><td style="text-align: right;color: #333333;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #333333;;">280000</td><td style="text-align: right;color: #333333;;">154509</td><td style="text-align: right;color: #333333;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;color: #333333;;">233999</td><td style="text-align: right;color: #333333;;">154515</td><td style="text-align: right;color: #333333;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;color: #333333;;">300999</td><td style="text-align: right;color: #333333;;">154747</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;color: #333333;;">300999</td><td style="text-align: right;color: #333333;;">154747</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;color: #333333;;">280000</td><td style="text-align: right;color: #333333;;">154878</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;color: #333333;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;color: #333333;;">235999</td><td style="text-align: right;color: #333333;;">154878</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;color: #333333;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;color: #333333;;">300999</td><td style="text-align: right;color: #333333;;">154956</td><td style="text-align: right;color: #333333;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;color: #333333;;">300999</td><td style="text-align: right;color: #333333;;">154956</td><td style="text-align: right;color: #333333;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;color: #333333;;">300999</td><td style="text-align: right;color: #333333;;">154956</td><td style="text-align: right;color: #333333;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;color: #333333;;">300999</td><td style="text-align: right;color: #333333;;">154956</td><td style="text-align: right;color: #333333;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;color: #333333;;">301999</td><td style="text-align: right;color: #333333;;">155000</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;color: #333333;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;color: #333333;;">300999</td><td style="text-align: right;color: #333333;;">155000</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;color: #333333;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,089,862
Messages
5,410,855
Members
403,331
Latest member
dignityy

This Week's Hot Topics

Top