Removing quotes from a formula calculation

0
votes

I have a test table below that represents a table on a much grander scale with more rows and columns

Date    key ID  Sample  Name    Business
test    test    test    test    test    
test    test    test    test    test    
test    test    test    test    test    
test    test    test    test    test    
test    test    test    test    test    
test    test    test    test    test

It is important to note that this table is populated by a copy and paste from another data source. I have a countif formula that gets the count of all instances of "test" in column "Business", but I run into a problem if the original data source that is copied over decides to add a column before the "Business" column. When I copy and paste the data, my countif formula is going to look for items in column(5), when it should now be looking in column(6). I created a formula below to catch this dynamically, so the countif range picks up the new column reference, but the calculation is adding double quotes around the cell range and giving me an error.

--Dynamic formula

=SUBSTITUTE(ADDRESS(1,MATCH("Business",A1:A5,0),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH("Business",A1:A5,0),4),"1","")

--New countif formula

=COUNTIF(SUBSTITUTE(ADDRESS(1,MATCH("Business",A:E,0),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH("Business",A:E,0),4),"1",""),"test")

When I highlight the dynamic formula and press F9, you can see how the double quotes are assigned to the formula, thus giving me the error, shown below. How do I fix this, so I can allow the formula to have the dynamic ability to always give me the count of the specified column regardless of how many columns are added or changed?

=COUNTIF("E:E","test")

Test

2
excelexcel-formula

2 Answers

2
votes

You can use Match to locate the column, and Index to return that column, and pass it to Countif

=COUNTIF(INDEX(1:1048576,,MATCH("Business",1:1,0)),"test")
1
votes

You can use INDIRECT to convert a string to a Range. So, =COUNTIF(INDIRECT("E:E"),"test") evaluates to =COUNTIF(E:E,"test"):

=COUNTIF(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH("Business",A:E,0),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH("Business",A:E,0),4),"1","")),"test")