Conditional SUM Q

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I have the following data layout

Col A to E (Row 5) with values say, 10,15,20,25,30

Coll A to E (Row 7) has values say, 15,10,10,30,35

Col A to E (Row 9) returns an "Under" or " " (blank) depending if Row
5 is more or less than Row 7. Thus A9 would show "Under"; D9 would
show "Under" and E9 would show "Under". B & C would show " ".

What I wish t do is add up the difference between A to E Row 5 and Row
7, but ONLY if Row 5 is less than Row 7 i.e. those colmns that show
"Under".

How would I do that via a formula? Hope I am making sense
 
In A9 write

=IF(A5<A7,"Under","")

Then drag it to the right till column E.

I hope I understood the question correctly. Let me know if it works.

Thanks.
 
or if you want to add row 5 and 7 only if the value in row 5 is less than
row 7...

=IF(A5<A7,SUM(A5,A7),"")

Drag it till column E.
 
If you mean add up the differences between the rows when row 5 is smaller
than row 7 then without any other steps try:

=SUMPRODUCT((A5:E5<A7:E7)*(A7:E7-A5:E5))

Answer 15.

If you mean add up row 7 where it is greater than row 5 then try:

=SUMPRODUCT((A5:E5<A7:E7)*A7:E7)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanks everyone. I'm going to pick Sandys as that returns the value in
one cell which I'm looing for
 
One question, what is the difference with the way the following is
expressed?

=SUMPRODUCT((A5:E5<A7:E7)*(A7:E7-A5:E5))

and

=SUMPRODUCT(--(A7:E7>A5:E5),(A7:E7-A5:E5))
 
The form

=SUMPRODUCT(--(A7:E7>A5:E5),(A7:E7-A5:E5))

is the form in which the SUMPRODUCT function multiplies two arrays
together (the result of --(A7:E7>A5:E5) and t he result of
(A7:E7-A5:E5)) and adds the resulting array - see

http://www.mcgimpsey.com/excel/doubleneg.html

The form

=SUMPRODUCT((A5:E5<A7:E7)*(A7:E7-A5:E5))

first multiplies the arrays, then passes the result off to the
SUMPRODUCT function, which adds the values in the array.

Practically, there's not very much difference, though testing found that
the first form was marginally faster. You probably won't notice a
difference on a modern machine until you start having large numbers of
SUMPRODUCT functions with large arrays.
 
Back
Top