# Lookup Column & Count Values in that Column

#### kromer12

##### New Member
Hello All,

I have a drop down that lists the column headers on a sheet "MyData". I need to use the value in the drop down to find the column on sheet "MyData" and then use a countifs() function in that column.

I am able to find the column number using this:
E4 - Drop down value
=MATCH(E4,MyData!\$A\$1:\$HD\$1,0)

I am able to perform my countifs() using a static column (picking column "MyData!E:E") using this:
C9 & B9 are both variable criteria for my countifs()
=COUNTIFS(MyData!E:E,">0",MyData!D:D,"<6",MyData!B:B,C9,MyData!A:A,B9)

I would like to use a formula (or however many needed) if at all possible to easily toggle between different drop down values.

Any help is appreciated!

Thanks,

Sean

#### Marcelo Branco

##### MrExcel MVP
Maybe something like this

=COUNTIF(INDEX(MyData!\$A:\$HD,0,MATCH(E4,MyData!\$A\$1:\$HD\$1,0)),">0")

It's a very simple example working with just one column, but it can be adapted to multiple columns (COUNTIFS) dealing with MATCH(...).
Assuming MATCH(E4,MyData!\$A\$1:\$HD\$1,0) returns 5 (column E), so, for example, to get column D try
MATCH(E4,MyData!\$A\$1:\$HD\$1,0)-1

Hope this helps

M.

#### kromer12

##### New Member
Works great! Thank you!

I also found something else that works:

=COUNTIF(INDIRECT("MyData!"&\$E\$5),">0")

Where E5 = "E:E" determined by the following code when E4 is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myE As String, Col As String, myCell As Long
Set MDetail = Worksheets("Month Detail")
Set MyData = Worksheets("MyData")
myCell = MyData.Range("A1:HD1").Find(MDetail.Range("E4").Value).Column
Col = Split(MyData.Cells(1, myCell).Address(1, 0), "\$")(0)
MDetail.Range("E5").Value = Col & "2:" & Col & "1000"

End If
End Sub

In case anyone is interested in alternatives

Thanks,

Sean

1,081,690
Messages
5,360,614
Members
400,592
Latest member

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...