Countif

[Genetu]

I am new to the group. Thanks to be a member. This is my problem I want to count each needed cell value at the end of the row. Here is the image I have attached.

In the image, the colored columns are the "counter" has to be applied. My boss wants 10 people who have more "E"s and "V"s, ("E" for Excellent & "V" for Very good. Then someone helped me. This is the code I've tried.

Sub Count_Excellents()
Dim ws As Worksheet
Dim rng As Range
Dim outputE As Range, outputV As Range

Set ws = Worksheets("Sheet1")
Set rng = ws.Range("B2:N2") 'this must be upto the end of the data range

Set outputE = ws.Range("B2").End(xlToRight).Offset(0, 1)
Set outputV = ws.Range("B2").End(xlToRight).Offset(0, 2)

outputE = Application.WorksheetFunction.CountIf(rng, "E")' for excellent
outputV = Application.WorksheetFunction.CountIf(rng, "V") 'for verygood
End Sub

Is there any one helped me by making this loop. Or any other method

 

[Ezguy4u]

Genetu Let's get the ball rolling. Now usually my programs generate more questions than answers. But at least it starts the conversation going. Sometimes the A plus students weigh in and will give us a program with 2 lines of code. Anyway, let the discussion begin, Be sure to adjust the loop to how many time you want to loop.

Sub Prog1()

Dim col As Long
Dim row As Long

col = 4
row = 2

For row = 2 To 9

Cells(row, 9) = Application.CountIf(Range(Cells(row, 4), Cells(row, 8)), "E")
Cells(row, 10) = Application.CountIf(Range(Cells(row, 4), Cells(row, 8)), "V")
Cells(row, 11) = Application.Sum(Range(Cells(row, 9), Cells(row, 10)))

Next row

End Sub

22-04-23.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	S.No	Name	ID	Experience	Academic	Training	Add Language	Apperance	Excellent	Very good	Total
2				G	F	V	E	E	2	1	3
3				E	E	V	P	P	2	1	3
4				V	P	G	E	E	2	1	3
5				P	G	E	E	F	2	0	2
6				E	V	G	F	G	1	1	2
7				P	G	E	E	V	2	1	3
8				V	G	E	E	V	2	2	4
9				G	E	E	F	P	2	0	2
Data

 

[kweaver]

Welcome to Mr. Excel.

First, please use XL2BB to illustrate the problem because no one wants to have to type your sample data.

Then, if V = very good, how can the total of the first person be 2? There are 0 Vs.
Lastly, must you do this using VBA?

 

[Genetu]

Genetu Let's get the ball rolling. Now usually my programs generate more questions than answers. But at least it starts the conversation going. Sometimes the A plus students weigh in and will give us a program with 2 lines of code. Anyway, let the discussion begin, Be sure to adjust the loop to how many time you want to loop.

Sub Prog1()

Dim col As Long
Dim row As Long

col = 4
row = 2

For row = 2 To 9

Cells(row, 9) = Application.CountIf(Range(Cells(row, 4), Cells(row, 8)), "E")
Cells(row, 10) = Application.CountIf(Range(Cells(row, 4), Cells(row, 8)), "V")
Cells(row, 11) = Application.Sum(Range(Cells(row, 9), Cells(row, 10)))

Next row

End Sub

22-04-23.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	S.No	Name	ID	Experience	Academic	Training	Add Language	Apperance	Excellent	Very good	Total
2				G	F	V	E	E	2	1	3
3				E	E	V	P	P	2	1	3
4				V	P	G	E	E	2	1	3
5				P	G	E	E	F	2	0	2
6				E	V	G	F	G	1	1	2
7				P	G	E	E	V	2	1	3
8				V	G	E	E	V	2	2	4
9				G	E	E	F	P	2	0	2
Data

Thanks Ezguy, to your strive for solution. Some Problems were solved. unsolved problems are examiners are in debating to add "Age" as additional requirement. This will change the number of columns (updated weekly/monthly). The number of rows are up to last row, I already fix it by adding a variable to last row (.end(xlup)). Thanks to your immediate answer.

 

[Genetu]

Welcome to Mr. Excel.

First, please use XL2BB to illustrate the problem because no one wants to have to type your sample data.

Then, if V = very good, how can the total of the first person be 2? There are 0 Vs.
Lastly, must you do this using VBA?

to be Asked question.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Rate given by examiner to the candidates
2	S.No.	Name	ID	Experience	Academic	Short term training	Additional language skill	Physical appearance	Count "E"	Count "V"
3	1	John	BCN/025	G	F	G	E	E	2	0
4	2	Sara	BCN/013	E	E	V	P	P				E= Excellent
5	3	Chan	BCN/004	V	P	G	E	E				V= Very good
6	4	Bruce	BCN/054	P	G	E	E	F				G= Good
7	5	Gili	BCN/006	E	V	G	F	G				F= Fair
8	6	Sam	BCN/018	P	G	E	E	V				P= Poor
9	7	Conor	BCN/068	V	G	E	E	V
10	8	Amanda	BCN/009	G	E	E	E	P
11	9	Benoit	BCN/016
12	10	Alex	BCN/011
13	11	Rachel	BCN/012
14	12	Kemal	BCN/020
15	13
Asked question

Cell Formulas
Range	Formula
I3	I3	=COUNTIF(D3:H3,"E")
J3	J3	=COUNTIF(D3:H3,"V")

First, I am asking apology to post my question like that. I don't know am I correct or not to upload a 'mini-sheet' using XL2BB. Anyways, I have to start with your first question. You're right the 1st person has no "V" but he has "E". To be selected, every candidate must have more than three scores of both "E &V". It may be 2:1, 3:0, or 3:2, etc... For your last question, the easiest solution is VBA. Because the candidates are more than 3600 from different branches.

 

[Ezguy4u]

I could do this all day long. Here is my second round of solutions. Now with this one you have to make sure the A column equals the last data entry. Also if you want that formula in I3, J3 & total in K3, let us know. Now if you are happy with just the answer, also let us know.

Sub Prog1A()

Dim n As Long
Dim col As Long
Dim row As Long
Dim LastCell As Long

LastCell = Cells(Rows.Count, "A").End(xlUp).row
col = 4

For row = 3 To LastCell

Cells(row, 9) = Application.CountIf(Range(Cells(row, 4), Cells(row, 8)), "E")
Cells(row, 10) = Application.CountIf(Range(Cells(row, 4), Cells(row, 8)), "V")
Cells(row, 11) = Application.Sum(Range(Cells(row, 9), Cells(row, 10)))

Next row

End Sub

22-04-23 rev a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Rate given by examiner to the candidates
2	S.No.	Name	ID	Experience	Academic	Short term training	Additional language skill	Physical appearance	Count "E"	Count "V"	Total Points
3	1	John	BCN/025	G	F	G	E	E	2	0	2
4	2	Sara	BCN/013	E	E	V	P	P	2	1	3	E= Excellent
5	3	Chan	BCN/004	V	P	G	E	E	2	1	3	V= Very good
6	4	Bruce	BCN/054	P	G	E	E	F	2	0	2	G= Good
7	5	Gili	BCN/006	E	V	G	F	G	1	1	2	F= Fair
8	6	Sam	BCN/018	P	G	E	E	V	0	0	0	P= Poor
9	7	Conor	BCN/068	V	G	E	E	V	2	2	4
10	8	Amanda	BCN/009	G	E	E	E	P	3	0	3
11	9	Benoit	BCN/016	P	G	E	E	F	2	0	2
12	10	Alex	BCN/011	E	V	G	F	G	1	1	2
13	11	Rachel	BCN/012	P	G	E	E	V	2	1	3
14	12	Kemal	BCN/020	V	G	E	E	V	2	2	4
Sheet1

 

[Genetu]

I could do this all day long. Here is my second round of solutions. Now with this one you have to make sure the A column equals the last data entry. Also if you want that formula in I3, J3 & total in K3, let us know. Now if you are happy with just the answer, also let us know.

Sub Prog1A()

Dim n As Long
Dim col As Long
Dim row As Long
Dim LastCell As Long

LastCell = Cells(Rows.Count, "A").End(xlUp).row
col = 4

For row = 3 To LastCell

Cells(row, 9) = Application.CountIf(Range(Cells(row, 4), Cells(row, 8)), "E")
Cells(row, 10) = Application.CountIf(Range(Cells(row, 4), Cells(row, 8)), "V")
Cells(row, 11) = Application.Sum(Range(Cells(row, 9), Cells(row, 10)))

Next row

End Sub

22-04-23 rev a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Rate given by examiner to the candidates
2	S.No.	Name	ID	Experience	Academic	Short term training	Additional language skill	Physical appearance	Count "E"	Count "V"	Total Points
3	1	John	BCN/025	G	F	G	E	E	2	0	2
4	2	Sara	BCN/013	E	E	V	P	P	2	1	3	E= Excellent
5	3	Chan	BCN/004	V	P	G	E	E	2	1	3	V= Very good
6	4	Bruce	BCN/054	P	G	E	E	F	2	0	2	G= Good
7	5	Gili	BCN/006	E	V	G	F	G	1	1	2	F= Fair
8	6	Sam	BCN/018	P	G	E	E	V	0	0	0	P= Poor
9	7	Conor	BCN/068	V	G	E	E	V	2	2	4
10	8	Amanda	BCN/009	G	E	E	E	P	3	0	3
11	9	Benoit	BCN/016	P	G	E	E	F	2	0	2
12	10	Alex	BCN/011	E	V	G	F	G	1	1	2
13	11	Rachel	BCN/012	P	G	E	E	V	2	1	3
14	12	Kemal	BCN/020	V	G	E	E	V	2	2	4
Sheet1

Ezguy4u, I admire your motivation to solve members' problems. I appreciate it. I modified your code to the last row and last column. But I need you to take the fame(prize). Did I violate the group rule?

 

[Ezguy4u]

Genetu I don't know all the rules here, but I figure we are doing ok. I was thinking, maybe we should put Application.ScreenUpdating = False at the top and Application.ScreenUpdating = True at the bottom. If you have lots of rows, this will stop the screen from updating. Thanks for the feedback.