Baffled comparing two arrays with IF/AND

[ejpuck]

Hello all,
I am brand new to this board, and my skills are limited. I am having trouble with an IF/And that compares 2 different arrays. The end goal is to determine if there is a spouse, and if that spouse is over 65. If true, I need "1,000" if not, "0".

Array number one is C35:C45 and contains either spouse, child, or nothing.

Array number 2 is I35:I45 and has the age of the corresponding individual from array one.

=IF(C35:C45="spouse","1,000",0) returns true, as does =IF(I35:45>64,"1000",0). But when I entered the same statements as an AND... =AND(C35:45="spouse","1,000",0) even though one of the cells was true, the formula returned false.

So of course when I tried to nest the if/and =IF(AND(C35:C45="spouse",I35:45>64),"1,000",0 it comes back false. Right now c35 says spouse, and I35 says 80.

What am I doing wrong, and how should I write this so it works.

 

[shg]

Maybe

=1000 * sumproduct((C35:C45="spouse")*(I35:I45>=65))

 

[ejpuck]

oops, OK, I forgot a very important piece. The answer returned is going to be a different cell reference if true. 1000 is not static. If there is a spouse, and the spouse is 65 or older, then I need a return of E9

 

[shg]

Change the 1000 to E9.

 

[ejpuck]

Change the 1000 to E9.

Works beautifully!!ray: Thank you so much. Any chance you could tell me why the IF/AND didn't work the way it should have? I want to learn from my mistakes.

 

[shg]

AND evaluates all of its arguments;

AND(C35:C45="spouse", I35:I45>64)

... would return TRUE if all of C were 'spouse" and all of I were > 64 (and if it were entered as an array formula).

 

[ejpuck]

Ahh, I thought it may be something like that. Thanks again.