COUNTIFS

[DJ038]

I have formula COUNTIFS with 3 conditions: '=COUNTIFS('Assets'!A1:FG1,B29,'Assets'!L2:L200,C28,'Assets'!AY2:AY200,"Yes")...I'm getting #VELUE output, why?

 

[DJ038]

OK , how about making an IF with a AND and count

We count to see if word exists in the row 1
COUNTIF('Assets'!A1:FG1,"Word") OR as you are using B29 COUNTIF(A1:FG1,B29)
And that will return true
NEXT
Countif('Assets'!L2:L200,C28)
put those into and AND

AND(COUNTIF(A1:FG1,B29),Countif('Assets'!L2:L200,C28))

Now if both are true we can count the YES again using a count
Countif('Assets'!AY2:AY200,"Yes")

SO
=IF( AND(COUNTIF(A1:FG1,B29),Countif('Assets'!L2:L200,C28)), Countif('Assets'!AY2:AY200,"Yes") , "" )

I have put into a simple sheet to show

Book2
	A	B	C	D	E	F	G	H	I
1	word
2									3
3							yes
4
5
6							yes
7			Associate				yes
8
Sheet1

Cell Formulas
Range	Formula
I2	I2	=IF(AND(COUNTIF(A1:H1,"Word"),COUNTIF(C1:C20,"associate")),COUNTIF(G1:G20,"yes"),"")

will that work for you

Sorry for my late response, I tried to work on this by myself

Thanks for the inputs, however formula =IF( AND(COUNTIF(A1:FG1,B29),Countif('Assets'!L2:L200,C28)), Countif('Assets'!AY2:AY200,"Yes") , "" ) is not what I need.

In column 'C' i have groups in row 'A' i have apps, and i need to know how many apps is used per group…how many 'yes' entries I have in the entire table by following this 'if' statements

My logic was if in column 'C' there is 'Admin' and in row 'A' there is 'Visio' count all 'Yes' entries in the entire table…outcome should be 1

My table has around 200 columns and over 1000 rows…

Below is an example....

Users​	Groups​	Word	Excel	PowerPoint	Outlook	SharePoint	Teams	Edge	Visio	MS Project
User 1​	Operations​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No
user 2​	IT​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No
User 3​	Admin​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
User 4​	Analytic​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
User 5​	Data​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 6​	Analytic​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 7​	Analytic​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No
User 8​	Operations​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
User 9​	Admin​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 10​	Data​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 11​	Operations​	Yes	Yes	Yes	Yes	No	Yes	Yes	No	No
User 12​	Operations​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
User 13​	Operations​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 14​	Data​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 15​	Analytic​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 16​	Data​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 17​	Operations​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
User 18​	Admin​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 19​	Admin​	Yes	Yes	Yes	Yes	No	Yes	Yes	Yes	No
User 20​	Admin​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 21​	Operations​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
User 22​	Analytic​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
User 23​	Analytic​	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
		Admin	Analytic	Data	IT	Operations
	Word​	5	6	4	1	7
	Excel​	5	6	4	1	7
	PowerPoint​	5	6	4	1	7
	Outlook​	5	6	4	1	7
	SharePoint​	4	6	4	1	6
	Teams​	5	6	4	1	7
	Edge​	5	6	4	1	7
	Visio​	1	1		1	2
	MS Project​	1	2			3

 

[Cubist]

Try this.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Users	Groups	Word	Excel	PowerPoint	Outlook	SharePoint	Teams	Edge	Visio	MS Project			Admin	Analytic	Data	IT	Operations
2	User 1	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No		Word	5	6	4	1	7
3	user 2	IT	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No		Excel	5	6	4	1	7
4	User 3	Admin	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes		PowerPoint	5	6	4	1	7
5	User 4	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes		Outlook	5	6	4	1	7
6	User 5	Data	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No		SharePoint	4	6	4	1	6
7	User 6	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No		Teams	5	6	4	1	7
8	User 7	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No		Edge	5	6	4	1	7
9	User 8	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes		Visio	1	1	0	1	2
10	User 9	Admin	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No		MS Project	1	2	0	0	3
11	User 10	Data	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
12	User 11	Operations	Yes	Yes	Yes	Yes	No	Yes	Yes	No	No
13	User 12	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
14	User 13	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
15	User 14	Data	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
16	User 15	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
17	User 16	Data	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
18	User 17	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
19	User 18	Admin	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
20	User 19	Admin	Yes	Yes	Yes	Yes	No	Yes	Yes	Yes	No
21	User 20	Admin	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
22	User 21	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
23	User 22	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
24	User 23	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
Sheet1

Cell Formulas
Range	Formula
N1:R1	N1	=TOROW(UNIQUE(SORT(B2:B24)))
M2:M10	M2	=TOCOL(C1:K1)
N2:R10	N2	=SUM(($B$2:$B$24=N$1)*($C$1:$K$1=$M2)*($C$2:$K$24="Yes"))
Dynamic array formulas.

 

[DJ038]

Try this.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Users	Groups	Word	Excel	PowerPoint	Outlook	SharePoint	Teams	Edge	Visio	MS Project			Admin	Analytic	Data	IT	Operations
2	User 1	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No		Word	5	6	4	1	7
3	user 2	IT	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No		Excel	5	6	4	1	7
4	User 3	Admin	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes		PowerPoint	5	6	4	1	7
5	User 4	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes		Outlook	5	6	4	1	7
6	User 5	Data	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No		SharePoint	4	6	4	1	6
7	User 6	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No		Teams	5	6	4	1	7
8	User 7	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No		Edge	5	6	4	1	7
9	User 8	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes		Visio	1	1	0	1	2
10	User 9	Admin	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No		MS Project	1	2	0	0	3
11	User 10	Data	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
12	User 11	Operations	Yes	Yes	Yes	Yes	No	Yes	Yes	No	No
13	User 12	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
14	User 13	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
15	User 14	Data	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
16	User 15	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
17	User 16	Data	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
18	User 17	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
19	User 18	Admin	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
20	User 19	Admin	Yes	Yes	Yes	Yes	No	Yes	Yes	Yes	No
21	User 20	Admin	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
22	User 21	Operations	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
23	User 22	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	Yes
24	User 23	Analytic	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No
Sheet1

Cell Formulas
Range	Formula
N1:R1	N1	=TOROW(UNIQUE(SORT(B2:B24)))
M2:M10	M2	=TOCOL(C1:K1)
N2:R10	N2	=SUM(($B$2:$B$24=N$1)*($C$1:$K$1=$M2)*($C$2:$K$24="Yes"))
Dynamic array formulas.

Many thanks, it works