Countifs and Sumifs with OR logic and Unique values

[maelstromm]

I did a search and couldn't find a good combination of how to use Countifs, Sumifs using UNIQUE values with multiple criteria for the data below.
Here is my ask:

1. Get a unique count of the number of times a Company appears along with the value of their business based on the name of the event.
2. Only be based on the Tier 1 and 2 relationship.
3. Change the lookup up to dynamically increase based on values in Row A. I don't expect there to be zero values in Column A

The formulas I have for Countifs and Sumifs are as follows.

• Company Count: SUM(COUNTIFS(F2:F11,"Yes",E2:E11,{"1","2"},A2:A11,"Event1")
• Company Value: SUM(SUMIFS(G2:G11,F2:F11,"Yes",E2:E11,{"1","2"},A2:A11,"Event1"))

This is based on creating a separate sheet that eliminates the duplicates in Column C.

Event	Date	Company	Name	Tier	Client	Account
Event 1	5/1/2020​	Company 1	Adam	1​	Yes	$10,000​
Event 1	5/1/2020​	Company 1	Bill	1​	Yes	$10,000​
Event 1	5/1/2020​	Company 2	Betty	2​	Yes	$2,000​
Event 1	5/1/2020​	Company 2	Sam	2​	Yes	$2,000​
Event 1	5/1/2020​	Company 3	Joe		No
Event 2	6/1/2020​	Company 4	Joan	2​	Yes	$4,000​
Event 2	6/1/2020​	Company 4	Sally	2​	Yes	$4,000​
Event 2	6/1/2020​	Company 4	Susie	3​	Yes	$1,000​
Event 2	6/1/2020​	Company 5	David	3​	Yes	$1,000​
Event 2	6/1/2020​	Company 6	Norm		No

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Event	Date	Company	Name	Tier	Client	Account
2	Event 1	01/05/2020	Company 1	Adam	1	Yes	£10,000			Company 1	2	20000
3	Event 1	01/05/2020	Company 1	Bill	1	Yes	£10,000			Company 2	2	4000
4	Event 1	01/05/2020	Company 2	Betty	2	Yes	£2,000			Company 3	0	0
5	Event 1	01/05/2020	Company 2	Sam	2	Yes	£2,000			Company 4	0	0
6	Event 1	01/05/2020	Company 3	Joe		No				Company 5	0	0
7	Event 2	01/06/2020	Company 4	Joan	2	Yes	£4,000			Company 6	0	0
8	Event 2	01/06/2020	Company 4	Sally	2	Yes	£4,000
9	Event 2	01/06/2020	Company 4	Susie	3	Yes	£1,000
10	Event 2	01/06/2020	Company 5	David	3	Yes	£1,000
11	Event 2	01/06/2020	Company 6	Norm		No
12
Data

Cell Formulas
Range	Formula
K2:K7	K2	=SUM(COUNTIFS(F:F,"Yes",E:E,{1,2},A:A,"Event 1",C:C,J2))
L2:L7	L2	=SUM(SUMIFS(G:G,F:F,"Yes",E:E,{1,2},A:A,"Event 1",C:C,J2))

 

[jasonb75]

Maybe I'm overthinking it, but this is how I interpreted the requirement (note that I've changed the table so that there are multiple valid results for 'Company 1').

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Event	Date	Company	Name	Tier	Client	Account		Company 1	Account	Count
2	Event 1	05/01/2020	Company 1	Adam	1	Yes	10000		Company 1	16000	3
3	Event 1	05/01/2020	Company 1	Bill	1	Yes	10000
4	Event 1	05/01/2020	Company 1	Betty	2	Yes	2000
5	Event 1	05/01/2020	Company 1	Sam	2	Yes	2000
6	Event 1	05/01/2020	Company 1	Joe		No
7	Event 1	06/01/2020	Company 1	Joan	2	Yes	4000
8	Event 1	06/01/2020	Company 1	Sally	2	Yes	4000
9	Event 1	06/01/2020	Company 1	Susie	3	Yes	1000
10	Event 1	06/01/2020	Company 1	David	3	Yes	1000
11	Event 1	06/01/2020	Company 1	Norm		No
Sheet3

Cell Formulas
Range	Formula
I2	I2	=UNIQUE(FILTER(C2:C11,(A2:A11="Event 1")*((E2:E11=1)+(E2:E11=2))*(F2:F11="Yes"),""))
J2	J2	=SUM(UNIQUE(FILTER(G2:G11,(A2:A11="Event 1")*((E2:E11=1)+(E2:E11=2))*(F2:F11="Yes"),"")))
K2	K2	=COUNT(UNIQUE(FILTER(G2:G11,(A2:A11="Event 1")*((E2:E11=1)+(E2:E11=2))*(F2:F11="Yes"),"")))

 

[Fluff]

You could well be right, we'll just have to wait for some clarification from the OP.

 

[maelstromm]

Thank you both. To be more clear, this is the output I am looking for:

Event Name	Company Count	Total Value
Event 1	2	$12000
Event 2	1	$4000

 

[Fluff]

Thanks for updating your profile, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Event	Date	Company	Name	Tier	Client	Account
2	Event 1	01/05/2020	Company 1	Adam	1	Yes	£10,000			Event 1	2	12000
3	Event 1	01/05/2020	Company 1	Bill	1	Yes	£10,000			Event 2	1	4000
4	Event 1	01/05/2020	Company 2	Betty	2	Yes	£2,000
5	Event 1	01/05/2020	Company 2	Sam	2	Yes	£2,000
6	Event 1	01/05/2020	Company 3	Joe		No
7	Event 2	01/06/2020	Company 4	Joan	2	Yes	£4,000
8	Event 2	01/06/2020	Company 4	Sally	2	Yes	£4,000
9	Event 2	01/06/2020	Company 4	Susie	3	Yes	£1,000
10	Event 2	01/06/2020	Company 5	David	3	Yes	£1,000
11	Event 2	01/06/2020	Company 6	Norm		No
12
Data

Cell Formulas
Range	Formula
J2:J3	J2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
K2:K3	K2	=ROWS(UNIQUE(FILTER($C$2:$C$100,($A$2:$A$100=J2)*($F$2:$F$100="Yes")*(($E$2:$E$100=1)+($E$2:$E$100=2)))))
L2:L3	L2	=SUM(UNIQUE(FILTER($G$2:$G$100,($A$2:$A$100=J2)*($F$2:$F$100="Yes")*(($E$2:$E$100=1)+($E$2:$E$100=2)))))
Dynamic array formulas.

 

[jasonb75]

I had much the same as @Fluff, just took me a bit longer to do it. Only thing I had different was the first formula,

=UNIQUE(FILTER(A2:A100,((E2:E100=1)+(E2:E100=2))*(F2:F100="Yes"),""))

to omit any event that doesn't meet the tier or client criteria.

 

[Fluff]

@jasonb75 Good thinking

 

[maelstromm]

Thank you both!! It worked like a charm. I preferred the first solution because I don't want to exclude any event, but added an ISERROR to avoid #CALC errors when an Event doesn't contain the filter criteria!

 

[Fluff]

Glad we could help & thanks for the feedback.