GET QTY in column J

[cprao]

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

 

[smartin]

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

In J2, then fill down:

=if(and(I2<7,F2<=0),B2,"")

 

[Pete_UK]

Try this:

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

Hope this helps.

Pete

 

[cprao]

Thank for the reply. 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]

Dear Pete, it is not working, I am getting sum of qty as 54. Please try again

 

[Pete_UK]

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

 

[cprao]

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

 

[Roger Govier]

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

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

 

[Peo Sjoblom]

How can he get 18 in the first row?




--


Regards,


Peo Sjoblom

 

[Roger Govier]

Hi Peo

I have absolutely no idea.
In his request, he keeps referring to tests on row 3, and getting a result
from B2.
I merely offered this amendment as a solution that satisfied both his
formula and his required result.

Seems very strange to me.

 

[cprao]

Thanks Roger & Peo for the interest. Peo is write. The formula has to work
for evey row. Please be in contact. Thanks in advance

 

[Spiky]

The formula they gave should work, and you should be able to copy it
down to every row and it should work in every row. In fact, the first
formula from smartin should not be able to return a zero in J2 (only
options should be "18", blank cell, or maybe an error message), yet
you said it did. Are you sure you typed it correctly?

And are you sure you typed your posts correctly? Notice the comments
from Peo and Roger, you are specifying 2 different rows in your
request. Is that really what you want? Accuracy is very important when
typing the rows and columns.

 

[cprao]

I have found the following formula. Can anybody give shorter than this. It
would be helpful.

=IF(AND(F2<=0,G2<7),INDEX(B$2:B$6,IF(COUNTIF(B1:B2,">0")>1,ROW(),ROW()-1))

 

[David Biddulph]

Perhaps =IF(AND(F2<=0,G2<7),INDEX(B$2:B$6,ROW()-1+(B1>0)*(B2>0))) ?

 

[Sandy Mann]

Using the same principle this is three characters fewer:

=INDEX(B$2:B$6,ROW()-1+(B1>0)*(B2>0))*((F2<=0)*(G2<7))

There is no *false* argument in either of the formulas so they both return
FALSE. This one returns 0, which may not suit the OP's requirements.

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