Counting using frequency formula with multiple criteria

[Beefboy]

I have a financial modeling issue I can not solve.

I am seeking:

1. The number of unique obligors with a senior debt principle value investments >0
2. The number of unique obligors with a principle value investment >0


[TABLE="width: 945"]
<tbody>[TR]
[TD="align: center"]A,1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]What I expect the results to be:[/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Obligor ID[/TD]
[TD="align: center"]Principle Value[/TD]
[TD="align: center"]Senior Debt
(Yes = 1)[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Unique Obligors with Senior Debt Investments w/ Principle > 0[/TD]
[TD="align: center"]Unique Obligors w/ Principle > 0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]115[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]160[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Result:[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody><colgroup><col style="text-align: center;" span="2"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup>[/TABLE]

​

I have been trying to utilize a derivative of the formula provided in the following thread but cant get it right.

=SUM(IF(FREQUENCY(IF(A6:A100=E2,IF(C6:C100<>"",C6:C100)),IF(A6:A100=E2,IF(C6:C100<>"",C6:C100))),1))
https://www.mrexcel.com/forum/excel...sing-frequency-formula-multiple-criteria.html

Your help would be greatly appreciated,

Chris

 

[AhoyNC]

If your ID's are numeric then formulas in E & F17.
If ID's are text then formulas in E & F18.
These are array formulas and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F
1	Obligor ID	Principle Value	Senior Debt		Unique Obligors with Senior Debt Investments w/ Principle > 0	Unique Obligors w/ Principle > 0
2			(Yes = 1)
3	1	100	1
4	2	200	1
5	2	300
6	3	110
7	4	115
8	5	120	1
9	5	160	1
10	6	200	1
11	7	300
12	8	0	1
13	8	100	1
14	9	0	1
15	9	0
16				Result:
17					5	8
18					5	8
Sheet

 

[Beefboy]

Worked like a charm! Much appreciated, a real life saver.

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.