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>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
<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 {}.</td></tr></table><br />


Excel 2013
ABCD
1DepartmentPublication Key# of AuthorsInterdisciplinarity
21P111
32P211
43P322
54P322
65P431
75P431
85P431
96P532
106P532
117P532
Sheet1
 
Upvote 0
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:
Upvote 0
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 {}.</td></tr></table><br />





Excel 2013
ABCDE
1DepartmentPublication Key# of AuthorsInterdisciplinarity (calculation result)Interdisciplinarity (what should be)
2251099154496111
3301999154501111
4233999154502111
5280000154509111
6233999154515111
7300999154747211
8300999154747211
9280000154878222
10235999154878222
11300999154956411
12300999154956411
13300999154956411
14300999154956411
15301999155000222
16300999155000222
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top