The ' character preceding text fields causing trouble?

[bbhgroup]

I am using a formula of the structure:

{=SUM(SUBTOTAL(function, OFFSET(ROW(Array_name)- ROW(start_row),,1) * A_TEST_CONDITION)} to add up values in a table that match the criteria of A_TEST_CONDITION.

A_TEST_CONDITION uses a column from the array and compares it to a fixed value.

It works really well when I have numbers in the array column to be tested, i.e., "1, 2, 3" but if I try to use a string variable, i.e., "ABC" the function comes back with a N/A error.

So if I have this array have 1 for the fixed value I get 36, for 2 I get 19.
1 14
1 22
2 10
2 09

However if I go to this:
1 14
ABC 22
2 10
2 09

It stops working and returns N/A. The only thing I can see if that I've noticed in the array when I type in the "ABC" into the it actually forces a quote in front of the character ('ABC) which I'm wondering if that is a problem somehow.

If I delete the portion of the formula with the test condition it works fine, likewise if it's all numeric also ok. Doesn't make sense. The cells are set to general format, i've tried 'text' as well to no avail...

Using Excel 2010. Any ideas out there?

 

[Steve=True]

I don't typically use arrays. I would have used a sumproduct formula.

This formula works on your sample for text or numbers:

=SUMPRODUCT(((A1:A4)=Condition)*(B1:B4))

I created a named range called condition = cell D2 that i used to change the criteria.

ABC = 22
1=14
2 =19

 

[bbhgroup]

Thanks for that help, the sumproduct is a simpler way to do it. I thought I had tried that before but your hint does work.

It did not solve the fundamental problem in my case though. What I have found is that one of the arrays I am using for a condition is a global variable of this :

DB_PL =INDIRECT("AllLoadData!$A2:$A"&COUNT(AllLoadData!$A:$A)+1)

I am building the DB_PL array dynamically as rows are added to the "AllLoadData" tab. What I found is that if I substitute a string (i.e. ABC) into the list instead of a number (1,2,3) the string is preceded by a ', as in 'ABC.
That causes this global array DB_PL to not return correctly. If I replace DB_PL in the equation with AllLoadData!$A2:$A48 then it all works fine.

So it seems to be the INDIRECT is somehow choking on the presence of the ' character.

Is there a better way to build arrays dynamically as the global variables?