Formula to Calculate Complex Count

[masouder]

I have a somewhat complex count to complete. I need to count the number of Accounts that have more than one Carrier where the Agent is Sally and the Date is Aug 1. In the example below the formula should return 1: only ACME Hardware has more than one Carrier.

Any help is appreciated.

Excel Question.xlsx
	A	B	C	D
1	Account	Carrier	Agent	Date
2	ACME Hardware	CalChoice	Sally	1-Aug
3	ACME Hardware	Covered California	Sally	1-Aug
4	ACME Hardware	Choice Builder	Sally	1-Aug
5	ACME Hardware	Choice Builder	Sally	1-Aug
6	ACME Hardware	Choice Builder	Sally	1-Aug
7	ACME Hardware	Choice Builder	Sally	1-Aug
8	ACME Hardware	Beam Dental	Sally	1-Aug
9	ACME Hardware	Beam Dental	Sally	1-Aug
10	South Street Market	Blue Shield	Sally	1-Aug
11	South Street Market	Blue Shield	Sally	1-Aug
12	South Street Market	Blue Shield	Sally	1-Aug
13	South Street Market	Blue Shield	Sally	1-Aug
14	Mid City Drugstore	MetLife	Sally	1-Aug
15	Mid City Drugstore	MetLife	Sally	1-Aug
16	Mid City Drugstore	MetLife	Sally	1-Aug
17	Mid City Drugstore	MetLife	Sally	1-Aug
18	Mid City Drugstore	MetLife	Sally	1-Aug
19	Mid City Drugstore	MetLife	Sally	1-Aug
20	Mid City Drugstore	MetLife	Sally	1-Aug
21	Mid City Drugstore	MetLife	Sally	1-Aug
22	Mid City Drugstore	MetLife	Sally	1-Aug
Sheet1 (2)

 

[masouder]

Not sure if this is related to deleting blank, but when I deleted rows where Joe is the Agent then it worked.