Need Assistance: Count Formula

[eganeyar]

Hello Everyone,

Greetings!

I would like to ask for your assistance if you can help me create a formula that would count the number of outage per circuit that happened within account's installation and disconnection date. The result is reflected in "Count of Circuit Outage Column".

Thank you in advance

Account	Circuit	Installed	Disconnected	Count of Circuit Outage			Circuit	Outage Date
AA1 <tbody> </tbody>	DHEC1234 <tbody> </tbody>	1/28/2016 <tbody> </tbody>	6/2/2016 <tbody> </tbody>	3			DHEC1234 <tbody> </tbody>	2/15/2016 <tbody> </tbody>
AA2 <tbody> </tbody>	DHEC1234 <tbody> </tbody>	2/11/2016 <tbody> </tbody>	7/3/2016 <tbody> </tbody>	2			DHEC1234 <tbody> </tbody>	5/3/2016 <tbody> </tbody>
AA3 <tbody> </tbody>	DHEC1234 <tbody> </tbody>	3/1/2016 <tbody> </tbody>	8/6/2016 <tbody> </tbody>	2			DHEC1234 <tbody> </tbody>	1/30/2016 <tbody> </tbody>
AA4 <tbody> </tbody>	DHEC1234 <tbody> </tbody>	4/7/2016 <tbody> </tbody>	9/12/2016 <tbody> </tbody>	2			DRHP5678 <tbody> </tbody>	3/6/2017 <tbody> </tbody>
AA5 <tbody> </tbody>	DHEC1234 <tbody> </tbody>	5/12/2016 <tbody> </tbody>	10/1/2016 <tbody> </tbody>	1			DRHP5679 <tbody> </tbody>	2/5/2017 <tbody> </tbody>
							DCEC4444 <tbody> </tbody>	1/4/2017 <tbody> </tbody>
							DCEC4445 <tbody> </tbody>	9/4/2016 <tbody> </tbody>
							DHEC1234 <tbody> </tbody>	7/5/2016 <tbody> </tbody>
							DHEC1234 <tbody> </tbody>	11/4/2016 <tbody> </tbody>
							DRHP5678 <tbody> </tbody>	4/1/2016 <tbody> </tbody>
							DHEC1234 <tbody> </tbody>	12/3/2016 <tbody> </tbody>

<tbody>
</tbody>

 

[Marcelo Branco]

Assuming the table Circuit - Outage Date in columns H:I in rows 1:12 (headers in row 1), try

E2 copied down
=COUNTIFS($H$2:$H$12,B2,$I$2:$I$12,">="&C2,$I$2:$I$12,"<="&D2)

Hope this helps

M.