| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
GET QTY in column J
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
GET QTY in column J
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,"") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
GET QTY in column J
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,"") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
GET QTY in column J
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
GET QTY in column J
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
GET QTY in column J
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 - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
GET QTY in column J
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
GET QTY in column J
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
| Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
| Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| what formula do i put for column m = column k minus column l in e. | Excel Discussion (Misc queries) | |||
Hybrid Mode
