Excel Formula - Return unique count of range based on multiple criteria from different tables

2
votes

I have three tables: Parties, Document Detail and Document. (Note, they are not table formatted, so all references are based on cell and sheet location, not table aliases)

I want to return a unique count of the parties in each Document using an Excel Formula. My problem is, I would usually use a =COUNTIFS() formula for this, which would be something like the following, which I would put in B2 of Sheet 3 (Document table):

=COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,Sheet1!A2)

But this will only return the count of one criteria at a time, not a count checking for all values of the parties table per document. I understand it should be able to be done with an array formula, but I can't figure it out. Bonus points if someone can figure out how to do it with a non-array formula!

Sheet1 - Parties Table

       A       
1|Parties          |
 +-----------------+
2|Education Officer|
3|Elder            |
4|Family Support   |
5|Interpreter      |

Sheet2 - Document Detail Table

       A            B
1 |Doc ID |  Party          |
  +-------+-----------------+
2 |FID0001|Education Officer|
3 |FID0001|Elder            |
4 |FID0001|Education Officer|
5 |FID0001|                 |
6 |FID0001|                 |
7 |FID0002|Elder            |
8 |FID0002|Interpreter      |
9 |FID0002|Family Support   |
10|FID0002|                 |

Sheet3 - (Desired Result) - Document Table

    A        B
1|Doc ID |Party Count|
 +-------+-----------+
2|FID0001|  2        |
3|FID0002|  3        |

TL:DR

What combination of Excel formulas can I use to return the number of unique parties referenced in each document?

1
excelexcel-formula

1 Answers

1
votes

Based on this answer by Barry Houdini and expanded to include the DocID criteria

Put a helper column on Sheet2, lets say in colum C

=IFERROR(1/COUNTIFS($B:$B,$B:$B,$A:$A,$A:$A),0)

and copy down for all data rows

Then, in Sheet3 Party Count Formula is

=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,$A:$A)

The non-Implicit Intersection versions

Sheet2 cell C1

=IFERROR(1/COUNTIFS($B:$B,$B1,$A:$A,$A1),0)

Sheet3 cell B2

=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,$A2)