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.


"Sean" wrote in message
ups.com...
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

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.


"Gary" wrote in message
...
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.


"Sean" wrote in message
ups.com...
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

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


Replace @mailinator.com with @tiscali.co.uk


"Sean" wrote in message
ups.com...
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

Thanks everyone. I'm going to pick Sandys as that returns the value in
one cell which I'm looing for

One way:

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

In article . com,
Sean wrote:

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

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:E7A5:E5),(A7:E7-A5:E5))

The form

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

is the form in which the SUMPRODUCT function multiplies two arrays
together (the result of --(A7:E7A5: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.




In article . com,
Sean wrote:

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:E7A5:E5),(A7:E7-A5:E5))