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)

 

[Eric W]

How about:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Account	Carrier	Agent	Date			Agent	Date	# of Accounts with over 1 carrier
2	ACME Hardware	CalChoice	Sally	8/1/2021			Sally	8/1/2021	1
3	ACME Hardware	Covered California	Sally	8/1/2021
4	ACME Hardware	Choice Builder	Sally	8/1/2021
5	ACME Hardware	Choice Builder	Sally	8/1/2021
6	ACME Hardware	Choice Builder	Sally	8/1/2021
7	ACME Hardware	Choice Builder	Sally	8/1/2021
8	ACME Hardware	Beam Dental	Sally	8/1/2021
9	ACME Hardware	Beam Dental	Sally	8/1/2021
10	South Street Market	Blue Shield	Sally	8/1/2021
11	South Street Market	Blue Shield	Sally	8/1/2021
12	South Street Market	Blue Shield	Sally	8/1/2021
13	South Street Market	Blue Shield	Sally	8/1/2021
14	Mid City Drugstore	MetLife	Sally	8/1/2021
15	Mid City Drugstore	MetLife	Sally	8/1/2021
16	Mid City Drugstore	MetLife	Sally	8/1/2021
17	Mid City Drugstore	MetLife	Sally	8/1/2021
18	Mid City Drugstore	MetLife	Sally	8/1/2021
19	Mid City Drugstore	MetLife	Sally	8/1/2021
20	Mid City Drugstore	MetLife	Sally	8/1/2021
21	Mid City Drugstore	MetLife	Sally	8/1/2021
22	Mid City Drugstore	MetLife	Sally	8/1/2021
Sheet12

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(--(FREQUENCY(IF(C2:C22=G2,IF(D2:D22=H2,IF(MATCH(A2:A22&"|"&B2:B22,A2:A22&"|"&B2:B22,0)=ROW(A2:A22)-ROW(A2)+1,MATCH(A2:A22,A2:A22,0)))),MATCH(A2:A22,A2:A22,0))>1))

 

[masouder]

That appears to work. Thank you!

 

[Joe4]

That appears to work. Thank you!

I have re-marked the "Solution".
In marking the solution, please mark the actual post that contains the solution (and not your post acknowldeging that someone has posted the correct solution).

Thanks

 

[masouder]

Actually, I discovered that sometimes the suggested formula works correctly and sometimes it does not. In the example below the formula should return 2 because ACME Hardware and Mid City Drugstore have more than one Carrier. If I change some of the Carriers for Mid City or rearrange the order of how the carriers appear then it sometimes works, but I cannot detect a pattern.

Any further help is greatly appreciated.

Excel Question.xlsx
	A	B	C	D	E	F	G
1	Account	Carrier	Agent	Date
2	ACME Hardware	CalChoice	Sally	1-Aug		Sally	1-Aug
3	ACME Hardware	Covered California	Sally	1-Aug
4	ACME Hardware	Choice Builder	Sally	1-Aug		1
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
12	South Street Market	Blue Shield	Sally	1-Aug
13	South Street Market	Blue Shield	Sally	1-Aug
17	Mid City Drugstore	Blue Shield	Sally	1-Aug
19	Mid City Drugstore	MetLife	Sally	1-Aug
20	Mid City Drugstore	Blue Shield	Sally	1-Aug
21	Mid City Drugstore	MetLife	Sally	1-Aug
22	Mid City Drugstore	MetLife	Sally	1-Aug
Sheet1 (2)

Cell Formulas
Range	Formula
F4	F4	=SUMPRODUCT(--(FREQUENCY(IF(C2:C22=F2,IF(D2:D22=G2,IF(MATCH(A2:A22&"|"&B2:B22,A2:A22&"|"&B2:B22,0)=ROW(A2:A22)-ROW(A2)+1,MATCH(A2:A22,A2:A22,0)))),MATCH(A2:A22,A2:A22,0))>1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dave Patton]

Try removing all blank rows.

 

[Eric W]

What version of Excel are you using? (It would help if you could update your user profile with that information.) I copied your mini-sheet to Excel 365, and it worked (got an answer of 2) immediately. I then copied it to Excel 2016, and it worked once I added the CSE. I even tried Excel 2000, but just got an error.

I thought the blank/hidden rows might have been an issue, so I played around with that for a bit, to no avail. On my 2016 version, I even got a result of 3 for the same data. Running it through Evaluate Formula showed it worked perfectly up to the last step, then gave a 3, making no sense at all.

In any case, try this variation, it works for me in 2016. Since you have to use CSE anyway, I used SUM instead of SUMPRODUCT. But the main change is changing the second array from MATCH to ROW:

=SUM(--(FREQUENCY(IF(C2:C22=F2,IF(D2:D22=G2,IF(MATCH(A2:A22&"|"&B2:B22,A2:A22&"|"&B2:B22,0)=ROW(A2:A22)-ROW(A2)+1,MATCH(A2:A22,A2:A22,0)))),ROW(A2:A22)-ROW(A2)+1)>1))

 

[masouder]

I am using Excel 2013. (I work with many clients, so I tend to stay a bit behind on version so that I don't create something that they cannot use.)

I tried the new formula, thank you, and got the same result, 1. Note that the issue appears to be related to the number of different Carriers. When I add a third carrier to Mid City it calculates 2, as expected. Note that in a larger example, I still cannot find any consistency other than for some Accounts if I add another Carrier or change the order of the Carriers then it works.

What is CSE?

 

[Dave Patton]

I tried the formula with Excel 2010. I deleted the blank rows and Array Entered the formula CSE Control Shift Enter; the result was 2.
N.B. Excel will add the curly brackets for Array Formula.

T202109a.xlsm
	F
4	2
4b

Cell Formulas
Range	Formula
F4	F4	=SUMPRODUCT(--(FREQUENCY(IF(C2:C17=F2,IF(D2:D17=G2,IF(MATCH(A2:A17&"|"&B2:B17,A2:A17&"|"&B2:B17,0)=ROW(A2:A17)-ROW(A2)+1,MATCH(A2:A17,A2:A17,0)))),MATCH(A2:A17,A2:A17,0))>1))
Press CTRL+SHIFT+ENTER to enter array formulas.

I may try with 365 later.

 

[masouder]

Ahh, Ctrl+Shift+Enter. I knew to press that for arrays, just didn't know what CSE meant.

What do you mean you deleted the blank rows?