Excel 2010 Formula: Finding a dynamic column based on string value, then count # of "0's" within that column

0

I have tried looking at many different potential avenues at solving this; INDEX, MATCH, DCOUNT, HLOOKUP with INDEX & MATCH, COUNTIF with INDEX & MATCH. What I'm looking for is a way to set the focus to a column that is being shifted around as other columns are added/deleted. There is a column header which is a fixed string value "Smartphones". The rows are different stores within my region and the sheet is for inventory levels and other metrics I need to track for my district manager. The data is kept on a different sheet than the summary report in which I'm using the formula.

Ideally I'd have a formula snippet that can be reused for varying formulas, but the immediate need (for my sanity) is for this formula to be able to "follow the column around" so I don't need to keep changing column letters almost every month. The simple formula counts the # of 0's in the specified column.

    =COUNTIF('Data'!AG:AG,0)

Thanks guys & gals!

raiif

Posted 2014-08-27T13:17:24.437

Reputation: 1

I should say I've already tried this http://superuser.com/questions/461075/dynamically-reference-a-named-table-column-via-cell-content-in-excel and I can't get it to work as the data set is quite large (A1:AO6172).

– raiif – 2014-08-27T13:19:32.740

hat I'm looking for is a way to set the focus to a column that is being shifted around as other columns are added/deleted Column references in formulas are updated automatically when columns get added or deleted! What is the exact issue you are facing? Give more details with some specific examples. – jjk_charles – 2014-08-27T13:56:46.933

Hi, sorry I should've specified this is Macro related. When I created the Macro with this formula, the Smartphone column (AG) had 6246 rows of which 637 cells within that column were 0. This month I have 6258 rows with 521 cells within that column 0. The Smartphone column is currently column AN. Six months ago there were 5723 rows. – raiif – 2014-08-27T14:33:18.370

So I have a Macro created with a specific column reference that I need to keep changing so I can total the 0's in the Smartphone column. The exact issue I am facing is having to edit the column letter anytime columns are changed. These columns are updated as there are new workbooks each month, and there are multiple workbooks that this needs to work in as I have similar but varied reports for multiple individuals. – raiif – 2014-08-27T14:40:00.630

For specific examples: July scenario: =COUNTIF('Data'!AF:AF,0) 6246 rows, formula result is 637. August scenario: =COUNTIF('Data'!AN:AN,0) 6258 rows, formula result is 521. – raiif – 2014-08-27T14:43:34.157

Answers

0

The easiest way I can think to do this is to create a table with your data.

enter image description here

The table name is located by choosing any cell in the table then click the design tab in the ribbon. The table name will be in the upper left corner.

enter image description here

Then use a formula to refer to the table and the column header. 

=COUNTIF(Table2[Smartphones], 0)

As you add columns and rows the table will update automatically, therefore your formula will look at the column named Smartphones (regardless where it is in the table) and count the number of cells with 0 in them.

CharlieRB

Posted 2014-08-27T13:17:24.437

Reputation: 21 303

Asked: 2014-08-27T13:17:24.437

Viewed: 625 times

Active: 2019-08-14T07:52:29.793