Function to exclude cells from formula

[andyp161]

Hi,

Imagine A1:A6 contained A,B,C,D,E,F and B1:B6 contained 1,2,3,4,5,6
If, for example, I wanted to apply a formula to B1:B6 but exclude an
cell in this array that met a condition in the adjacent cell in A1:A
how would I go about this??

Kind regard

 

[Aladin Akyurek]

=SUMPRODUCT(--(ISNA(MATCH($A$1:$A$6,$D$2:$D$4,0))),$B$1:$B$6)

would sum any figure in B1:B6 that corresponds to any value in A1:A6 which
is not in D24.

=SUMPRODUCT(--(ISNA(MATCH($A$1:$A$6,$D$2:$D$4,0))),--($A$1:$A$6<>""),$B$1:$B
$6)

The same as above, but it would also excludes empty or blanks cells from
A1:A6.

 

[andyp161]

Thanks for the feedback. What about if I wanted Excel to sum B1:B6, bu
not to sum any cell in this array, where the letter 'c' was in th
adjacent cell in array A1:A6.

Many thank

 

[Aladin Akyurek]

One of, depending on how you want to treat empty/blank cells in A1:A6...

=SUMIF($A$1:$A$6,"<>C",$B$1:$B$6)

=SUMPRODUCT(--($A$1:$A$6<>"C"),--($A$1:$A$6<>""),$B$1:$B$6)