Lookup Column & Count Values in that Column

kromer12

New Member
Joined
Jun 25, 2015
Messages
5
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
MyData!$A$1:$HD$1 - Data headers
=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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
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)
If Target.Address = "$E$4" Then
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
 
Upvote 0

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

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
Back
Top