Dynamic Range names & sumifs error

[nikegeo]

I'm having a hard time getting the sumifs working with my dynamic range names ive created.

Here are the named ranges and formulas:
CLMarket- =OFFSET('Global Performance Highlights'!$F$44,0,0,COUNTA('Global Performance Highlights'!$F:$F)-1)
CLOffice- =OFFSET('Global Performance Highlights'!$G$44,0,0,COUNTA('Global Performance Highlights'!$G:$G)-1)
CLRevenue- =OFFSET('Global Performance Highlights'!$K$44,0,0,COUNTA('Global Performance Highlights'!$K:$K)-1)

I am referencing k44 because thats the table that hold my data and any additions i make to it id like the formula to automatically calculate.

My sumifs formula is as follows: =SUMIFS(CLRevenue,CLOffice,B2,CLMarket,A2)

So im trying to sum revenue if based on office and market

i keep getting a #value. Not sure what im doing wrong. Any help would be great.

 

[blueturtle]

Thank you Mike. Your solution also helped me with an identical problem with rows of data coming in from an Access query.

 

[Aladin Akyurek]

Thank you Mike. Your solution also helped me with an identical problem with rows of data coming in from an Access query.

You have everything coming over as text, I suppose.

 

[blueturtle]

You have everything coming over as text, I suppose.

I have a combination of columns of text and numbers and then calculations based on various conditions. And the data will only grow which is why I have spent hours searching for a solution.

 

[Aladin Akyurek]

I have a combination of columns of text and numbers and then calculations based on various conditions. And the data will only grow which is why I have spent hours searching for a solution.

I don't think that each field is numeric as well as text. Post $3 picks out a numeric field which must be the most complete (a key field in Access whose data type is AutoNumber fullfills this requirement) and constructs the dynamic ranges. If there is no numeric field, a text field that is most complete will also do: just replace 9.99999999999999E+307 with REPT("z",255). The set up in post #3 does not flounder on empty cells as COUNTA does and MATCH is faster than COUNTA. As a side note, even if there were no fields which are only numeric or text, the set up can be modified to accommodate.