Need help with countif

[camilleso]

Hi, I need help with a countif formula. I have one column of data with 458 rows; 4 options and want to count how many times each option shows up in the row.

Column A:
Option 1
Option 2
Option 3
Option 4
Option 1
Option 2
Option 3
Option 4

 

[camilleso]

There's a follow up to my previous question. I also need help with creating a formula that will look for Option 1, Option 2, Option 3, Option 4 and if there is a 'Yes' next to them, sum that total. I want see how many 'Yes' exist in Option 1, Option 2, Option 3, Option 4 respectively.

 

[Sam_D_Ben]

You mean something like PIVOT.

 

[camilleso]

A pivot table can produce this I guess. Since some information may change within this data set I didn't want to use a pivot and only stay on one page to view this data. If option 1 has a "yes" next to it, I want to count how many option 1 "yes" exist, same for option 2, 3 and 4.

 

[Sam_D_Ben]

Something like this.

Excel 2013/2016
	A	B	C	D	E	F
1	OPTIONS	Count
2	Option 1	1			Option 1	2
3	Option 2	1			Option 2	2
4	Option 3	1			Option 3	2
5	Option 4	1			Option 4	2
6	Option 1	1			Option 5	0
7	Option 2	1			Total	8
8	Option 3	1
9	Option 4	1
Sheet1

Cell Formulas
Range	Formula
F2	=SUMIFS($B$2:$B$9,$A$2:$A$9,$E2)
B2	=VALUE(IF(OR(A2=$E$2,A2=$E$3,A2=$E$4,A2=$E$5),"1","0"))

 

[Sam_D_Ben]

Error.

 

[Sam_D_Ben]

Or,

Excel 2013/2016
	A	B	C	D	E
1	OPTIONS		OPTIONS	Y/N	Count Y/N
2	Option 1		Option 1	YES	2
3	Option 2		Option 2	YES	2
4	Option 3		Option 3	YES	2
5	Option 4		Option 4	YES	2
6	Option 1		Option 5	NO	0
7	Option 2
8	Option 3
9	Option 4
Sheet1

Cell Formulas
Range	Formula
D2	=IFERROR(IF(VLOOKUP(C2,$A$2:$A$9,1,0)=C2,"YES","NO"),"NO")
E2	=IF(D2="YES",COUNTIF($A$2:$A$9,C2),"0")

 

[Peter_SSs]

I have one column of data with 458 rows; 4 options and want to count how many times each option shows up in the row.

I want see how many 'Yes' exist in Option 1, Option 2, Option 3, Option 4 respectively.

To me that sounds like these 2 formulas copied down,

Excel Workbook
	A	B	C	D	E	F
1	Option	Yes/No		Option	Count	Count with Yes
2	Option 3	Yes		Option 1	6	4
3	Option 3	Yes		Option 2	5	2
4	Option 1	Yes		Option 3	9	5
5	Option 1	Yes		Option 4	12	6
6	Option 1	No
7	Option 4	Yes
8	Option 3	No
9	Option 4	Yes
10	Option 3	No
11	Option 4	Yes
12	Option 1	No
13	Option 2	Yes
14	Option 1	Yes
15	Option 4	No
16	Option 3	No
17	Option 3	No
18	Option 2	No
19	Option 4	No
20	Option 2	Yes
21	Option 4	No
22	Option 2	No
23	Option 4	Yes
24	Option 3	Yes
25	Option 1	Yes
26	Option 3	Yes
27	Option 4	No
28	Option 4	Yes
29	Option 4	No
30	Option 4	Yes
31	Option 2	No
32	Option 3	Yes
33	Option 4	No
Counts

 

[Sam_D_Ben]

Peter_SSs, Yes, Yes, thats what OP wants. My brain dint work in that way. Thank you