Editing an array formula...?

[Rameses]

Hi all

Please may I have some advice on editing the following formula

=SUM((C2:C47="Y")*(W2:W47={"N",-1,-2,-3,-4}))

I forgot to add some extra conditions to column C

These are to ensure either "Y", "P", "S", "A" are met in column C (I'm not changing column w)

many thanks once again

all comments are much appreciated

Rameses

 

[Aladin Akyurek]

Hi all

Please may I have some advice on editing the following formula

=SUM((C2:C47="Y")*(W2:W47={"N",-1,-2,-3,-4}))

I forgot to add some extra conditions to column C

These are to ensure either "Y", "P", "S", "A" are met in column C (I'm not changing column w)

many thanks once again

all comments are much appreciated

Rameses

Try: Control+shift+enter, not just enter...

=SUM(
    IF(C2:C47="Y",
    IF(ISNUMBER(MATCH(W2:W47,{"N",-1,-2,-3,-4},0)),
      1)))

and...

=SUM(
    IF(ISNUMBER(MATCH(C2:C47,{"Y", "P", "S", "A"},0)),
    IF(ISNUMBER(MATCH(W2:W47,{"N",-1,-2,-3,-4},0)),
      1)))

 

[jasonb75]

Original thread

http://www.mrexcel.com/forum/showthread.php?t=453125

 

[Rameses]

Thankyou Aladin & Jason

Aladin's response worked perfectly

many thanks

Rameses