Find number of times a value is true in column B OR C for a given value in column A (likely Countif or Sumproduct)

[Kotetsu534]

I am quite new at using Excel formulas, and wonder if there's a way I can extract what I want from a table like below:

Team	Achievement 1	Achievement 2
A	Yes	Yes
B	Yes	Yes
C	No	No
A	No	Yes
C	Yes	Yes

I'd like to be able to add a column to another sheet which searches for a particular "Team" in the first column, then searches for whether there's a Yes in *either* "Achievement 1" or "Achievement 2" (so just using Countifs won't work - it would double count if both columns have a "Yes") then totals the number of rows in which that's true. So for Team A, my column would return 2, for Team B, it would return 1, and for Team C, it would also return 1.

Would really appreciate any help and sorry if this is a very basic question - I couldn't find an example after some googling so came here.

 

[lrobbo314]

***

 

[lrobbo314]

Using a helper column.

Book1.xlsb
	A	B	C	D	E	F	G
1	Team	Achievement 1	Achievement 2	Helper		Team	Total
2	A	Yes	Yes	1		A	2
3	B	Yes	Yes	1		B	1
4	C	No	No	0		C	1
5	A	No	Yes	1
6	C	Yes	Yes	1
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=SORT(UNIQUE(A2:A6))
G2:G4	G2	=SUMIF($A$2:$A$6,F2,$D$2:$D$6)
D2:D6	D2	=OR(B2="Yes",C2="Yes")+0
Dynamic array formulas.

 

[Fluff]

Hi & welcome to Mr Excel.
Another option

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Team	Achievement 1	Achievement 2		Team	Total
2	A	Yes	Yes		A	2
3	B	Yes	Yes		B	1
4	C	No	No		C	1
5	A	No	Yes
6	C	Yes	Yes
7
Data

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT(($A$2:$A$6=E2)*((($B$2:$B$6="Yes")+($C$2:$C$6="yes"))>0))

 

[jtakw]

Hi,

Another way:

Use D2 formula will replicate results
Use E2 formula will only populate 1st instance.

Book3.xlsx
	A	B	C	D	E
1	Team	Achievement 1	Achievement 2
2	A	Yes	Yes	2	2
3	B	Yes	Yes	1	1
4	C	No	No	1	1
5	A	No	Yes	2
6	C	Yes	Yes	1
Sheet1042

Cell Formulas
Range	Formula
D2:D6	D2	=SUMPRODUCT((A$2:A$6=A2)*ISNUMBER(SEARCH("Yes",B$2:B$6&C$2:C$6)))
E2:E6	E2	=IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$6=A2)*ISNUMBER(SEARCH("Yes",B$2:B$6&C$2:C$6))),"")

 

[lrobbo314]

Was trying to figure one out without the helper too. I like Fluff's, pretty straight forward.

Book1.xlsb
	A	B	C	D	E	F
1	Team	Achievement 1	Achievement 2		Team	Total
2	A	Yes	Yes		A	2
3	B	Yes	Yes		B	1
4	C	No	No		C	1
5	A	No	Yes
6	C	Yes	Yes
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=SORT(UNIQUE(A2:A6))
F2:F4	F2	=SUMPRODUCT(($A$2:$A$6=E2)*IFERROR((($B$2:$B$6="Yes")+($C$2:$C$6="Yes"))^0,0))
Dynamic array formulas.

 

[Kotetsu534]

Thanks very much everyone for your swift responses - especially Fluff's method which I've just implemented and works well. I understood the logic at work too - pretty intuitive.

 

[Fluff]

Glad we could help & thanks for the feedback.