Forum Discussion
Removing double quotes around strings returned by formulae
I've constructed a cell range using concatenate which works fine except that when it is returned, Excel sticks double quotes around them and my match function fails. I've tried trim&substitute. I've...
=COUNTIFS(StaffTBL[Position],"Head Teacher",StaffTBL[Gender],"MALE",StaffTBL[Dec-2022],"Active")
StaffTBL: Table Name
Position, Gender, Dec-2022: Columns Name
i want to change the selection of the month column according to the current month ..so , i have column for months (Sep-2022,Oct-2022,.....Jul-2023) , i used countifs as shown above and i tried to change month column dynamically by using Hlookup formula inside countifs formula , and it worked ,but when the Hlookup brings column name it keeps put hidden quotes ("" "") at first and end of column name, if U use hlookup separately it will bring correct column name but when i put it inside countifs it brings column name as "StaffTBL[Dec-2022]"
In addition to mathetes comments above I would like to add that this should be a NEW post. The original post was solved.
As for your issue we need to see what you tried. I'm not sure what HLOOKUP returned "StaffTBL[Dec-2022]" I think you want your HLOOKUP to return the column header name and then put that inside the [ ] but that might be easier using INDEX( MATCH ()) or if you have Excel 365 then just use XLOOKUP. But maybe try something like:
=COUNTIFS(StaffTBL[Position],"Head Teacher",StaffTBL[Gender],"MALE",StaffTBL[ INDEX(StaffTBL[#Headers], MATCH( ... ) ) ],"Active")
As for your issue we need to see what you tried. I'm not sure what HLOOKUP returned "StaffTBL[Dec-2022]" I think you want your HLOOKUP to return the column header name and then put that inside the [ ] but that might be easier using INDEX( MATCH ()) or if you have Excel 365 then just use XLOOKUP. But maybe try something like:
=COUNTIFS(StaffTBL[Position],"Head Teacher",StaffTBL[Gender],"MALE",StaffTBL[ INDEX(StaffTBL[#Headers], MATCH( ... ) ) ],"Active")