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!
The easiest way I can think to do this is to create a table with your data.
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.
Then use a formula to refer to the table and the column header.
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.