CONDITIONAL SUM ("OR")

F

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

T. Valko

One way:

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

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

Jacob Skaria

Try this...

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

If this post helps click Yes
 
F

FARAZ QURESHI

Man that's great Jacob!

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

XClent.
 
J

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
 
J

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
 
F

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top