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.


Thanks guys & gals!

  • I should say I've already tried this superuser.com/questions/461075/… and I can't get it to work as the data set is quite large (A1:AO6172). – raiif Aug 27 '14 at 13:19
  • 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 Aug 27 '14 at 13:56
  • 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 Aug 27 '14 at 14:33
  • 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 Aug 27 '14 at 14:40
  • 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 Aug 27 '14 at 14:43

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.