How can he get 18 in the first row?




--


Regards,


Peo Sjoblom

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

You have had 2 answers which should have worked, except you say in your
note that your want the value in B2, when you are testing against the
values in I3 and F3.
If this is correct, then just amend the formula to
=IF(AND(I3<7,F3<=0),B2,0)


--
Regards
Roger Govier

"cprao" wrote in message
...
I am afraid it is not working. Because what I require is
(qty)B2(i.e.18) in J3 when (Bal due)F3<=0 and (days)I<7. Hope it is
clear
now. yours formula gives me zero instead of 18
--
cprao


"Pete_UK" wrote:

Yes, I misunderstood what you wanted. Try this in J3:

=IF(AND(I3<7,F3<=0),B3,0)

Then copy down the column.

Hope this helps.

Pete

On Aug 18, 4:25 pm, cprao wrote:
Dear Pete, it is not working, I am getting sum of qty as 54. Please
try again
--
cprao



"Pete_UK" wrote:
Try this:

=SUMPRODUCT((I2:I5<7)*(F2:F5<=0)*(J2:J5))

Hope this helps.

Pete

On Aug 17, 6:30 pm, cprao wrote:
Bill date qty Bill amt Pay.date Amt Bal. Days
A B C D E F I J
18-Jul 18 800 19-Jul 900 -100 1 18
20-Jul 18 900 19-Jul 200 600 -1 0
20-Jul 0 0 24-Jul 700 0 4 18
30-Jul 18 900 28-Jul 400 500 -2 0
30-Jul 0 0 4-Aug 600 -100 5 18
As given in the above tabel I wish to GET the qty fig whenever
the Days are
below 7 days with various with condition that bal.is <=0.
Please help
with excel worksheet function.
Thanks in advance
--
cprao- Hide quoted text -

- Show quoted text -