# Thread: Reference cells in an array

1. I've successfully written a conditional sum array that sums Range 2 IF the value in Range 1 is greater than that in a specific cell, to which I point using "<"&\$B60 in this example:

=SUMIF(\$A\$1:\$A\$50,"<"&\$B60,\$C\$1:\$C\$50)

In this example, \$A1:\$A50 is Range 1 and \$C\$1\$:\$C50 is Range 2.

This is entered as an array with the curly brackets. All is well.

I have another set of arrays which tests for two conditions:

=SUM(IF(\$A\$1:\$A\$50="Value1",IF(\$C\$1:\$C\$50="Value2" ,\$D\$1:\$D\$50,"")))

Likewise, all is well.

But when I try to point back to a cell as I do in the first example, I get a #VALUE! error.

=SUM(IF(\$A\$1:\$A\$50,"<"&\$B\$60,IF(\$C\$1:\$C\$50="Value1 ,\$D\$1:\$D\$50,"")))

It appears I can make Value 1 a reference with "<"&\$B60 in the first example, but not in the second. Does this -- whatever the proper terminology here -- only work when there is one conditional test but not when there are two? Or am I doing something wrong?

thanks

Don

2. Hi Don,

Try:
=SUM(IF(\$A\$1:\$A\$50="Value1")*(\$C\$1:\$C\$50="Value2") ,\$D\$1:\$D\$50))
and:
=SUM(IF(\$A\$1:\$A\$50<\$B\$60)*(\$C\$1:\$C\$50="Value1"),\$D \$1:\$D\$50))

3. FYI, your first SUMIF formula does not need to be array entered.

If you are using 2007 or later you can use SUMIFS for multiple conditions, otherwise I'd use SUMPRODUCT (again, no array-entry required):

=SUMPRODUCT((\$A\$1:\$A\$50<\$B\$60)*(\$C\$1:\$C\$50="Value1 ),\$D\$1:\$D\$50)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•