Function to exclude cells from formula

  • Thread starter Thread starter andyp161
  • Start date Start date
A

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
 
=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 D2:D4.

=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.
 
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
 
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)
 
Back
Top