CONDITIONAL SUM ("OR")

[FARAZ QURESHI]

Sure would oblige if any of you experts devise a formula to calculate the sum
of column E of the following table, wherever an "X" is present in any of the
corresponding cells of Column A, B, C or D (i.e. resulting to 268326):

X Z Y Y 48421
Y W Z Z 60492
X X X X 22073
W X W W 19299
Z Z Y X 34706
Y W Y W 83035
V Z Z V 64431
X W X W 76684
Y Y Z Z 15703
V Z W W 11638
Z X W V 15573
V X Z X 11751
X Y Y X 39819

 

[T. Valko]

One way:

=SUMPRODUCT(--(MMULT(--(A113="x"),{1;1;1;1})>0),E1:E13)

Note that the MMULT function is limited to a range of 5461 rows.

 

[Jacob Skaria]

Try this...

=SUMPRODUCT(--((A1:A25="X")+(B1:B25="X")+(C1:C25="X")+(D125="X")>0),--E1:E25)

If this post helps click Yes

 

[FARAZ QURESHI]

Man that's great Jacob!

Never had the idea of using ">" for neglecting double counting!

XClent.

 

[Jarek Kujawa]

one way:

=SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C$13="X")+($D$1:$D
$13="X")+($E$1:$E$13="X"),$F$1:$F$13))

this is an array-formula so CTRL+SHIFT+ENTER it instead of simply
entering

adjust ranges to suit

HIH

 

[Jarek Kujawa]

corrected

=SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C$13="X")+($D$1:$D
$13="X"),$F$1:$F$13))

sorry

 

[Jacob Skaria]

FARAZ ,thanks for your feedback..

 

[FARAZ QURESHI]

Further corrected

=SUM(IF(($A$1:$A$13="X")+($B$1:$B$13="X")+($C$1:$C$13="X")+($D$1:$D$13="X"),$E$1:$E$13))