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

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

In J2, then fill down:

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

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

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


"smartin" wrote:

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

In J2, then fill down:

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

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

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 -

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 -

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 -

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 -

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.

--
Regards
Roger Govier

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

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


"Roger Govier" wrote:

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.

--
Regards
Roger Govier

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

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.

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


"Peo Sjoblom" wrote:

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 -

Perhaps =IF(AND(F2<=0,G2<7),INDEX(B$2:B$6,ROW()-1+(B10)*(B20))) ?
--
David Biddulph

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

Using the same principle this is three characters fewer:

=INDEX(B$2:B$6,ROW()-1+(B10)*(B20))*((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


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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Perhaps =IF(AND(F2<=0,G2<7),INDEX(B$2:B$6,ROW()-1+(B10)*(B20))) ?
--
David Biddulph

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