Count If with Breaks

[cheesiepoof05]

I am trying to come up with a formula for Column S (count of Broad Names with 1 or more 1's in the unique name count, split by field ID" which will tell me how many Broad Names are being used per Field ID. I added in the correct answer already just manually but I need to figure out a formula to replicate that.

O	P	Q	R	S
Field ID	BROAD NAME	UNIQUE NAME	Unique Name Being Used (1=Yes, 0=No)	Count of Broad Names With 1 or more 1's in the Unique Name Count, Split by Field ID
400	DOGS	German Shepherd	1	3
400	DOGS	Poodle	0	3
400	CATS	Persian	1	3
400	CATS	Siamese	1	3
400	CATS	Abyssinian	0	3
400	REPTILES	Crocodile	0	3
400	REPTILES	Chameleon	0	3
400	AMPHIBIANS	Frog	1	3
573	BLUES	Carolina	1	2
573	BLUES	Cobalt	1	2
573	BLUES	Turquoise	0	2
573	REDS	Magenta	0	2
573	REDS	Candy Apple	0	2
573	REDS	Auburn	0	2
573	REDS	Wine	0	2
573	GREENS	Avocado	1	2
573	GREENS	Mint	0	2
573	GREENS	Chartreuse	1	2

<colgroup><col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1248;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3296;"> <col width="149" style="width: 112pt; mso-width-source: userset; mso-width-alt: 4768;"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 3680;"> <col width="174" style="width: 131pt; mso-width-source: userset; mso-width-alt: 5568;"> <tbody> </tbody>

Thank you!

 

[Eric W]

Try:

	O	P	Q	R	S	T
1	Field ID	BROAD NAME	UNIQUE NAME	Unique Name Being Used (1=Yes, 0=No)	Count of Broad Names With 1 or more 1's in the Unique Name Count, Split by Field ID
2	400	DOGS	German Shepherd	1	3
3	400	DOGS	Poodle	0	3
4	400	CATS	Persian	1	3
5	400	CATS	Siamese	1	3
6	400	CATS	Abyssinian	0	3
7	400	REPTILES	Crocodile	0	3
8	400	REPTILES	Chameleon	0	3
9	400	AMPHIBIANS	Frog	1	3
10	573	BLUES	Carolina	1	2
11	573	BLUES	Cobalt	1	2
12	573	BLUES	Turquoise	0	2
13	573	REDS	Magenta	0	2
14	573	REDS	Candy Apple	0	2
15	573	REDS	Auburn	0	2
16	573	REDS	Wine	0	2
17	573	GREENS	Avocado	1	2
18	573	GREENS	Mint	0	2
19	573	GREENS	Chartreuse	1	2

<tbody>
</tbody>

Roadmap​

Array Formulas Cell Formula S2 {=SUM(SIGN(FREQUENCY(IF($O$2:$O$19=O2,IF($R$2:$R$19=1,MATCH($P$2:$P$19,$P$2:$P$19,0))),ROW($O$2:$O$19)- ROW($O$2)+1)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[cheesiepoof05]

Thanks Eric, that worked perfectly. I appreciate your help!

 

[Eric W]

Glad to help!