countif names

salimnore

New Member
Joined
Jun 5, 2014
Messages
7
Office Version
  1. 365
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
ABCD
1forenamesurnameexpected resultactual test result
2JonathanEdwardsED
3RichardCarterBC
4PaulPhillipsCD
5TheresaRossAB
6NicoleBennettCB
7ShawnKellyCB
8TammyFosterEC
9JeremyGonzalesFD
10EarlMartinezBA
11RaymondJenkinsBB
12BobbySimmonsAB
13DebraLongDC
14JustinRobinsonDC
15JoyceBakerEF
16LarryJonesCC
17RyanCampbellBB
18FrancesMillerAA*
19PhilipButlerA*A
20ChristinaSanchezAA
21CarlosBryantA*A
22GaryCoxCB
23RobertWrightBC
24CraigLewisBC
25JamesHernandezDC
26IreneMooreA*B
Data




countif_names.xlsx
ABCDEFGHIJKLMNOPQ
1
2Actual Grades
3UGFEDCBAA*
4Estimated gradesU000000000Below grade
5G000000000
6F000010000on grade
7E001011000
8D000003000Above grade
9C000011300
10B000003210
11A000000211
12A*000000120
Sheet1
Cell Formulas
RangeFormula
D4D4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"U")
E4E4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"G")
F4F4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"F")
G4G4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"E")
H4H4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"D")
I4I4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"C")
J4J4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"B")
K4K4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"A")
L4L4=COUNTIFS(Data!$C$2:$C$26,"U",Data!$D$2:$D$26,"A~*")
D5D5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"U")
E5E5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"G")
F5F5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"F")
G5G5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"E")
H5H5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"D")
I5I5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"C")
J5J5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"B")
K5K5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"A")
L5L5=COUNTIFS(Data!$C$2:$C$26,"G",Data!$D$2:$D$26,"A~*")
D6D6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"U")
E6E6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"G")
F6F6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"F")
G6G6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"E")
H6H6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"D")
I6I6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"C")
J6J6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"B")
K6K6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"A")
L6L6=COUNTIFS(Data!$C$2:$C$26,"F",Data!$D$2:$D$26,"A~*")
D7D7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"U")
E7E7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"G")
F7F7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"F")
G7G7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"E")
H7H7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"D")
I7I7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"C")
J7J7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"B")
K7K7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"A")
L7L7=COUNTIFS(Data!$C$2:$C$26,"E",Data!$D$2:$D$26,"A~*")
D8D8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"U")
E8E8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"G")
F8F8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"F")
G8G8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"E")
H8H8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"D")
I8I8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"C")
J8J8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"B")
K8K8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"A")
L8L8=COUNTIFS(Data!$C$2:$C$26,"D",Data!$D$2:$D$26,"A*")
D9D9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"U")
E9E9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"G")
F9F9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"F")
G9G9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"E")
H9H9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"D")
I9I9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"C")
J9J9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"B")
K9K9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"A")
L9L9=COUNTIFS(Data!$C$2:$C$26,"C",Data!$D$2:$D$26,"A~*")
D10D10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"U")
E10E10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"G")
F10F10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"F")
G10G10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"E")
H10H10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"D")
I10I10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"C")
J10J10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"B")
K10K10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"A")
L10L10=COUNTIFS(Data!$C$2:$C$26,"B",Data!$D$2:$D$26,"A~*")
D11D11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"U")
E11E11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"G")
F11F11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"F")
G11G11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"E")
H11H11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"D")
I11I11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"C")
J11J11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"B")
K11K11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"A")
L11L11=COUNTIFS(Data!$C$2:$C$26,"A",Data!$D$2:$D$26,"A~*")
D12D12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"U")
E12E12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"G")
F12F12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"F")
G12G12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"E")
H12H12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"D")
I12I12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"C")
J12J12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"B")
K12K12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"A")
L12L12=COUNTIFS(Data!$C$2:$C$26,"A~*",Data!$D$2:$D$26,"A~*")
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

kennypete

Active Member
Joined
Apr 19, 2008
Messages
270
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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"):
VBA 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
1619300608862.png
1619300573799.png


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:
1619300729535.png

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

New Member
Joined
Jun 5, 2014
Messages
7
Office Version
  1. 365
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

Active Member
Joined
Apr 19, 2008
Messages
270
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,269
Messages
5,657,747
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top