countif names

[salimnore]

Hi

Thank you for taking the time to read this post, I just want to know how to reveal the names of the countif formulas for my criteria using vba. I've attached 2 sheets the second sheet is my 2 way table. I just want to be able to click on a cell eg i8 and reveal the three names that match my criteria. Many thanks for any help.

Stay safe.

countif_names.xlsx
	A	B	C	D
1	forename	surname	expected result	actual test result
2	Jonathan	Edwards	E	D
3	Richard	Carter	B	C
4	Paul	Phillips	C	D
5	Theresa	Ross	A	B
6	Nicole	Bennett	C	B
7	Shawn	Kelly	C	B
8	Tammy	Foster	E	C
9	Jeremy	Gonzales	F	D
10	Earl	Martinez	B	A
11	Raymond	Jenkins	B	B
12	Bobby	Simmons	A	B
13	Debra	Long	D	C
14	Justin	Robinson	D	C
15	Joyce	Baker	E	F
16	Larry	Jones	C	C
17	Ryan	Campbell	B	B
18	Frances	Miller	A	A*
19	Philip	Butler	A*	A
20	Christina	Sanchez	A	A
21	Carlos	Bryant	A*	A
22	Gary	Cox	C	B
23	Robert	Wright	B	C
24	Craig	Lewis	B	C
25	James	Hernandez	D	C
26	Irene	Moore	A*	B
Data

countif_names.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2				Actual Grades
3				U	G	F	E	D	C	B	A	A*
4		Estimated grades	U	0	0	0	0	0	0	0	0	0				Below grade
5			G	0	0	0	0	0	0	0	0	0
6			F	0	0	0	0	1	0	0	0	0				on grade
7			E	0	0	1	0	1	1	0	0	0
8			D	0	0	0	0	0	3	0	0	0				Above grade
9			C	0	0	0	0	1	1	3	0	0
10			B	0	0	0	0	0	3	2	1	0
11			A	0	0	0	0	0	0	2	1	1
12			A*	0	0	0	0	0	0	1	2	0
Sheet1

Cell Formulas
Range	Formula
D4	D4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"U")
E4	E4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"G")
F4	F4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"F")
G4	G4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"E")
H4	H4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"D")
I4	I4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"C")
J4	J4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"B")
K4	K4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"A")
L4	L4	=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"A~*")
D5	D5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"U")
E5	E5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"G")
F5	F5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"F")
G5	G5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"E")
H5	H5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"D")
I5	I5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"C")
J5	J5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"B")
K5	K5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"A")
L5	L5	=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"A~*")
D6	D6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"U")
E6	E6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"G")
F6	F6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"F")
G6	G6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"E")
H6	H6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"D")
I6	I6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"C")
J6	J6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"B")
K6	K6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"A")
L6	L6	=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"A~*")
D7	D7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"U")
E7	E7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"G")
F7	F7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"F")
G7	G7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"E")
H7	H7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"D")
I7	I7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"C")
J7	J7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"B")
K7	K7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"A")
L7	L7	=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"A~*")
D8	D8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"U")
E8	E8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"G")
F8	F8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"F")
G8	G8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"E")
H8	H8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"D")
I8	I8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"C")
J8	J8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"B")
K8	K8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"A")
L8	L8	=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"A*")
D9	D9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"U")
E9	E9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"G")
F9	F9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"F")
G9	G9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"E")
H9	H9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"D")
I9	I9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"C")
J9	J9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"B")
K9	K9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"A")
L9	L9	=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"A~*")
D10	D10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"U")
E10	E10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"G")
F10	F10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"F")
G10	G10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"E")
H10	H10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"D")
I10	I10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"C")
J10	J10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"B")
K10	K10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"A")
L10	L10	=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"A~*")
D11	D11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"U")
E11	E11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"G")
F11	F11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"F")
G11	G11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"E")
H11	H11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"D")
I11	I11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"C")
J11	J11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"B")
K11	K11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"A")
L11	L11	=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"A~*")
D12	D12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"U")
E12	E12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"G")
F12	F12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"F")
G12	G12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"E")
H12	H12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"D")
I12	I12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"C")
J12	J12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"B")
K12	K12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"A")
L12	L12	=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"A~*")

 

[kennypete]

Here's one a way to reveal the names, which uses the ability of VBA to run SQL queries on the table:

1. Create the following code on Sheet1 (right click Sheet1, the one with your COUNTIFS grid, and "View Code"):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim strCell As String, arrFormula() As String, strAct As String, strEst As String

' Take the selection's formula and split it to extract
' the actual (strAct) and estimated (strEst) grades
strCell = Selection.Formula
arrFormula = Split(strCell, """")
strAct = arrFormula(3): If strAct = "A~*" Then strAct = "A" & Chr(42)
strEst = arrFormula(1): If strEst = "A~*" Then strEst = "A" & Chr(42)

' Early binding, ref: Microsoft ActiveX Data Objects 6.1 Library
Dim cn As Object, rs As Object, output As String, sql As String, strConnect As String

' Connecting to the Data Source
Set cn = New ADODB.Connection
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & _
                "\" & ThisWorkbook.Name & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
cn.Open strConnect

' Run the SQL SELECT Query
sql = "SELECT * FROM [Data$] WHERE ([Data$].[expected result] ALIKE """ & strEst & """ AND [Data$].[actual test result] ALIKE """ & strAct & """)"
Set rs = cn.Execute(sql)

If Not rs.BOF And Not rs.EOF Then
   ' If rows are returned
    Do
       output = output & rs(0) & " " & rs(1) & Chr(9) & rs(2) & " (est.)" & Chr(9) & rs(3) & " (act.)" & vbNewLine
       rs.MoveNext
    Loop Until rs.EOF
Else
    ' If no rows are returned
    output = "None"
End If

MsgBox output

'---Clean up---
rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing

' Negate the double click
SendKeys "{esc}"

End Sub

2. Ensure that the References (Tools, References) has the library shown selected

3. Now when you double-click any of the cells in the grades sheet you should get the answer. E.g. double-clicking Sheet1 cell I10:

More could be done to change this, of course, to things like adding custom presentation of the data in something other than a message box, restricting the range of the double-click to only grade intersection cells and not other cells, etc., but this does the core requirement.

 

[salimnore]

Hi,
I have found a solution for vba regarding my criteria. Can anyone explain this to in simple terms please. Here is the code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Est, Act, Fin As String
Dim xRow As Integer

Cancel = True
If Target <> 0 Then
Est = Cells(Target.Row, 3)
Act = Cells(3, Target.Column)
With Sheets("Data")
For xRow = 2 To .Cells(2, 3).End(xlDown).Row
If .Cells(xRow, 3) = Est And .Cells(xRow, 4) = Act Then
Fin = Fin & vbLf & .Cells(xRow, 1) & " " & .Cells(xRow, 2)
End If
Next xRow
End With
MsgBox Right(Fin, Len(Fin) - 1)
End If
End Sub

 

[kennypete]

Hi,
I have found a solution for vba regarding my criteria. Can anyone explain this to in simple terms please. Here is the code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Est, Act, Fin As String
Dim xRow As Integer

Cancel = True
If Target <> 0 Then
Est = Cells(Target.Row, 3)
Act = Cells(3, Target.Column)
With Sheets("Data")
For xRow = 2 To .Cells(2, 3).End(xlDown).Row
If .Cells(xRow, 3) = Est And .Cells(xRow, 4) = Act Then
Fin = Fin & vbLf & .Cells(xRow, 1) & " " & .Cells(xRow, 2)
End If
Next xRow
End With
MsgBox Right(Fin, Len(Fin) - 1)
End If
End Sub

Notwithstanding I gave you a VBA solution to this that works perfectly, the code you have put here does a whole bunch of hard coded things:

1. Grabs the Est grade to look up by getting column 3 relative to the cell you click [ Est = Cells(Target.Row, 3) ]
2. Grabs the Actual grade to look up by getting row 3 relative to the cell you click [ Act = Cells(3, Target.Column) ]
3. Loops through the Data sheet from row 2 through to the end of the data [ With Sheets("Data") For xRow = 2 To .Cells(2, 3).End(xlDown).Row ]
4. Checks columns 3 and 4 for rows in Data where the Est and Act match [ If .Cells(xRow, 3) = Est And .Cells(xRow, 4) = Act Then ]
5. Appends the first and last names to a variable "Fin" [ Fin = Fin & vbLf & .Cells(xRow, 1) & " " & .Cells(xRow, 2) ]

Personally I think this is not great because, although it looks concise, it's not extensible. For example, if someone changes the Data sheet to add "Age" to Column C, leaving everything else unchanged, this code will fail. Likewise, if you move the Sheet1 results table by inserting a row or column it will also fail. Conversely, my code, except if you change the column headings' text in Sheet1 (as it uses those for the query) should continue to work just fine and, IMHO, is easier to understand.