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

by raiif   Last Updated August 14, 2019 08:01 AM

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!



Answers 1


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
CharlieRB
August 27, 2014 15:18 PM

Related Questions


Updated January 24, 2018 17:01 PM

Updated March 23, 2017 19:01 PM

Updated July 11, 2017 14:01 PM

Updated October 14, 2017 08:01 AM