(Geoff Hicks) wrote in message . com...
Hi,

I am trying to figure out why a two variable query on an array doesn't
work.

The example below illustrates the problem:

A B C
1 Item Code Value
2 8 on 2
3 13 off 24
4 5 off 60
5 14 on 59
6 7 off 37
7 11 on 107
8 12 on 101
9 10 off 9
10 3 on 60
11 9 off 14

329 ={SUM(IF(B2:B11="on",C2:C11))}
173 ={SUM(IF(A2:A11<10,C2:C11))}
0 ={SUM(IF(AND(A2:A11<10,B2:B11="on"),C2:C11))}

The sum testing one variable (ie: "code") works as does the other one
variable sum (testing "Item") but the sum testing both variables
fails.

Anyone have a solution or an explanation?

Bye for now,

Geoff.

Robbie A

Try,
=SUMPRODUCT((A2:A11<10)*(B2:B11="on")*(C2:C11))
or array formula
=SUM(IF((A2:A11<10)*(B2:B11="on"),(C2:C11)))
Cecil

"Robbie Armstrong" wrote in message
m...
(Geoff Hicks) wrote in message
. com...
Hi,

I am trying to figure out why a two variable query on an array doesn't
work.

The example below illustrates the problem:

A B C
1 Item Code Value
2 8 on 2
3 13 off 24
4 5 off 60
5 14 on 59
6 7 off 37
7 11 on 107
8 12 on 101
9 10 off 9
10 3 on 60
11 9 off 14

329 ={SUM(IF(B2:B11="on",C2:C11))}
173 ={SUM(IF(A2:A11<10,C2:C11))}
0 ={SUM(IF(AND(A2:A11<10,B2:B11="on"),C2:C11))}

The sum testing one variable (ie: "code") works as does the other one
variable sum (testing "Item") but the sum testing both variables
fails.

Anyone have a solution or an explanation?

Bye for now,

Geoff.

Robbie A