How to Apply a combination of Index & Match Functions

I have attached a sample of what I am trying to do. Basically, sheet 1 maintains my raw data. each column in sheet 1 features data specific to a community. this data is comprised of both numeric and text values. On sheet 2 i have the production table i am trying to generate. I use a flag in sheet 1, such as "1" in the top row. For numeric values, the basic sumif function works great, but whenever it refers to a cell in sheet 1 with text, it brings back "0", what am i missing? Thanks in advance.