Re: $$Excel-Macros$$ Stock position - Reg
Difference items need one more conditions Mothilal On Sun, Apr 15, 2012 at 4:19 PM, Rajan_Verma wrote: > ** ** > > Stock = Opening Stock + Purchase – Sales , and there is no opening stock. > So P-S > > =SUMIF(A2:D7,"P",C2:C7)-SUMIF(A2:D7,"S",C2:C7) > > ** ** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *jmothilal > *Sent:* Apr/Sun/2012 02:31 > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ Stock position - Reg > > ** ** > > Thanks i am updating > > Mothilal > > On Sun, Apr 15, 2012 at 2:24 PM, Haseeb A > wrote: > > You can get this with just one SUMPRODUCT, > > =SUMPRODUCT((B$2:B2=B2)*(A$2:A2={"P","S"}),C$2:C2*{1,-1}) > > Which is on the file in the last reply. > > > > ___ > HTH, Haseeb > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > -- > To post to this group, send email to excel-macros@googlegroups.com > > > > > -- > > *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex > :Otteri, Vellore-2* > > ** ** > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > -- > To post to this group, send email to excel-macros@googlegroups.com > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > -- > To post to this group, send email to excel-macros@googlegroups.com > -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Stock position - Reg
Stock = Opening Stock + Purchase - Sales , and there is no opening stock. So P-S =SUMIF(A2:D7,"P",C2:C7)-SUMIF(A2:D7,"S",C2:C7) From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of jmothilal Sent: Apr/Sun/2012 02:31 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Stock position - Reg Thanks i am updating Mothilal On Sun, Apr 15, 2012 at 2:24 PM, Haseeb A wrote: You can get this with just one SUMPRODUCT, =SUMPRODUCT((B$2:B2=B2)*(A$2:A2={"P","S"}),C$2:C2*{1,-1}) Which is on the file in the last reply. ___ HTH, Haseeb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- J.Mothilal : Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Stock position - Reg
Thanks i am updating Mothilal On Sun, Apr 15, 2012 at 2:24 PM, Haseeb A wrote: > You can get this with just one SUMPRODUCT, > > =SUMPRODUCT((B$2:B2=B2)*(A$2:A2={"P","S"}),C$2:C2*{1,-1}) > > Which is on the file in the last reply. > > > ___ > HTH, Haseeb > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > -- > To post to this group, send email to excel-macros@googlegroups.com > -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Stock position - Reg
You can get this with just one SUMPRODUCT, =SUMPRODUCT((B$2:B2=B2)*(A$2:A2={"P","S"}),C$2:C2*{1,-1}) Which is on the file in the last reply. ___ HTH, Haseeb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Stock position - Reg
Thanks it working . also i find this formula =SUMPRODUCT(--(($A$2:A2="P")*($B$2:$B2=B2))*$C$2:C2)-SUMPRODUCT(--(($A$2:A2="S")*($B$2:$B2=B2))*$C$2:C2) On Sun, Apr 15, 2012 at 2:16 PM, Haseeb A wrote: > Hello Mothilal, > > See the attached. if you are on XL 2007 or later use SUMIFS > > ___ > HTH, Haseeb > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > -- > To post to this group, send email to excel-macros@googlegroups.com > -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Stock position - Reg
Hello Mothilal, See the attached. if you are on XL 2007 or later use SUMIFS ___ HTH, Haseeb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Stock.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Stock position - Reg
I want to find different items sums PURCHASE / SALES ITEM QTY STOCK P LG DVD WRITER 4 4 P LG DVD WRITER 5 9 S LG DVD WRITER 1 8 P LG DVD WRITER 1 9 P LG DVD WRITER 10 19 S LG DVD WRITER 4 15 p 512 MB DDR RAM 1 1 p 18.5 Monitor 1 1 p 500 GB Hard disk 1 1 S 512 MB DDR RAM 1 0 S 18.5 Monitor 1 0 S 500 GB Hard disk 1 0 Thanks with On Sun, Apr 15, 2012 at 12:11 PM, Maries wrote: > Hi, > > Find the attachment... > > > > On Sun, Apr 15, 2012 at 10:27 AM, jmothilal wrote: > >> >> PURCHASE / SALES ITEM QTY STOCK >> P LG DVD WRITER 1 1 >> P LG DVD WRITER 5 6 >> S LG DVD WRITER 1 5 >> P LG DVD WRITER 1 6 >> P LG DVD WRITER 10 16 >> S LG DVD WRITER 4 12 >> >> >> >> > > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > -- > To post to this group, send email to excel-macros@googlegroups.com > -- *J.Mothilal : **Universal Computer Systems : # 16, Brindavan Complex :Otteri, Vellore-2* -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Stock position - Reg
Hi, Find the attachment... On Sun, Apr 15, 2012 at 10:27 AM, jmothilal wrote: > > PURCHASE / SALES ITEM QTY STOCK > P LG DVD WRITER 1 1 > P LG DVD WRITER 5 6 > S LG DVD WRITER 1 5 > P LG DVD WRITER 1 6 > P LG DVD WRITER 10 16 > S LG DVD WRITER 4 12 > > > > -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Test.xls Description: MS-Excel spreadsheet