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

A,1	B	C	D			What I expect the results to be:
2	Obligor ID	Principle Value	Senior Debt (Yes = 1)			Unique Obligors with Senior Debt Investments w/ Principle > 0	Unique Obligors w/ Principle > 0
3	1	100	1			1	1
4	2	200	1			1	1
5	2	300
6	3	110					1
7	4	115					1
8	5	120	1			1	1
9	5	160	1
10	6	200	1			1	1
11	7	300					1
11	8	0	1
12	8	100	1			1	1
12	9	0	1
12	9	0
					Result:	5	8

<tbody>
</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>

​

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.