Re: $$Excel-Macros$$ Conditional Sum or Sumproduct.
THANKS. BUT IT SERVE THE PURPOSE PARTIALLY...IN ADDITION I HAVE TO CONSIDER
ONLY FY 2009-10 WHEREAS FORMULA TAKES BEYOND 31/03/2010.3
IS IT POSSIBLE TO REVISE THE FORMULA.
REGARDS,
C.G.KUMAR
On Fri, Oct 8, 2010 at 2:19 PM, ashish koul wrote:
> check the atatchment see if it helps
>
>
>
> On Fri, Oct 8, 2010 at 12:18 PM, Chandra Gupt Kumar <
> kumar.bemlmum...@gmail.com> wrote:
>
>> Objective is to calculate Sale for the financial year 2009-10 (April
>> 2009 – March 2010 )for each staff no. Data are in sheet named ‘DATA’ in
>> different column. It Starts with Staff No. then Start Date, End Date, Sale
>> Type, and Amount (*It is* *Sale per month during start and end period)*.
>>
>>
>>
>> 1. For example : if we have to calculate sale made by staff no.9465
>> during financial Year 2009-10, we see staff no.9465 and start with
>> identifying April 09 sale is at Row no 8 & monthly sale were 12850.00 (cell
>> E8) for April to June 10. Further, from July 09 to March 10, sale were 29100
>> per month. So, total sale for the financial Year should come : 12850*3(
>> April – June 09) +29100*9( July – March 10) =300450.00. Output required
>> against this staff no is 300450.00
>>
>> 2. Next if we calculate for Staff no.17442 , it comes 0 (Zero).
>> Because last data for him is for the period 01/07/2005 to 31/10/2007.
>>
>> 3. If we proceed to calculate for Staff No.17822, output should be
>> 118200.00 . This is calculated in following manner; he sold for 9750 per
>> month from 01/10/2008 to 30/09/2009 ,since we are determining only for April
>> 09 to March 10, we will consider 9750 for 6 months i.e. April 09- September
>> 09. Further, sale for the period 01/10/2009 to 31/12/ is/expected to
>> 9950.00. We again have to consider only period for April 09 to March 10, and
>> hence sale is :9950*6 (for October to March 10). Total sale by Staff No.
>> 17822 is 118200.00 {(9750*6(April-Sep09 months) +9950*6(Sept 09-March 10) =
>> 118200.)
>>
>>
>>
>> Hope it helps you to solve the crux .
>>
>>
>>
>>
>>
>> Regards,
>>
>>
>>
>> C.G.Kumar
>>
>> AMW-2
>>
>>
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-mac...@googlegroups.com] *On Behalf Of *Aindril De
>> *Sent:* Friday, October 08, 2010 11:35 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Conditional Sum or Sumproduct.
>>
>>
>>
>> Hi C.G.,
>>
>>
>>
>> Could you please elaborate more on what condition you would like to put in
>> here?
>>
>>
>>
>> Regards,
>>
>> Andy
>>
>> On Fri, Oct 8, 2010 at 10:34 AM, C.G.Kumar
>> wrote:
>>
>> I am looking for conditional sum i.e. sum based on criteria. But neither
>> Sumproduct nor SUMIFS and Pivot serve the purpose.
>>
>> Attached sample file . Hope you will be able to solve the crux.
>>
>>
>>
>>
>> Regards,
>>
>>
>>
>> C.G.Kumar
>>
>>
>>
>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>>
>> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>>
>>
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this g
RE: $$Excel-Macros$$ Conditional Sum or Sumproduct.
Objective is to calculate Sale for the financial year 2009-10 (April 2009 -
March 2010 )for each staff no. Data are in sheet named 'DATA' in different
column. It Starts with Staff No. then Start Date, End Date, Sale Type, and
Amount (It is Sale per month during start and end period).
1. For example : if we have to calculate sale made by staff no.9465
during financial Year 2009-10, we see staff no.9465 and start with
identifying April 09 sale is at Row no 8 & monthly sale were 12850.00 (cell
E8) for April to June 10. Further, from July 09 to March 10, sale were 29100
per month. So, total sale for the financial Year should come : 12850*3(
April - June 09) +29100*9( July - March 10) =300450.00. Output required
against this staff no is 300450.00
2. Next if we calculate for Staff no.17442 , it comes 0 (Zero).
Because last data for him is for the period 01/07/2005 to 31/10/2007.
3. If we proceed to calculate for Staff No.17822, output should be
118200.00 . This is calculated in following manner; he sold for 9750 per
month from 01/10/2008 to 30/09/2009 ,since we are determining only for April
09 to March 10, we will consider 9750 for 6 months i.e. April 09- September
09. Further, sale for the period 01/10/2009 to 31/12/ is/expected to
9950.00. We again have to consider only period for April 09 to March 10, and
hence sale is :9950*6 (for October to March 10). Total sale by Staff No.
17822 is 118200.00 {(9750*6(April-Sep09 months) +9950*6(Sept 09-March 10) =
118200.)
Hope it helps you to solve the crux .
Regards,
C.G.Kumar
AMW-2
From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com]
On Behalf Of Aindril De
Sent: Friday, October 08, 2010 11:35 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Conditional Sum or Sumproduct.
Hi C.G.,
Could you please elaborate more on what condition you would like to put in
here?
Regards,
Andy
On Fri, Oct 8, 2010 at 10:34 AM, C.G.Kumar
wrote:
I am looking for conditional sum i.e. sum based on criteria. But neither
Sumproduct nor SUMIFS and Pivot serve the purpose.
Attached sample file . Hope you will be able to solve the crux.
Regards,
C.G.Kumar
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall
<http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
> &ref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall
<http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
> &ref=ts
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Conditional Summation.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Conditional Sum or Sumproduct.
Hi C.G., Could you please elaborate more on what condition you would like to put in here? Regards, Andy On Fri, Oct 8, 2010 at 10:34 AM, C.G.Kumar wrote: > I am looking for conditional sum i.e. sum based on criteria. But neither > Sumproduct nor SUMIFS and Pivot serve the purpose. > > Attached sample file . Hope you will be able to solve the crux. > > > Regards, > > C.G.Kumar > > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
$$Excel-Macros$$ Conditional Sum or Sumproduct.
I am looking for conditional sum i.e. sum based on criteria. But neither Sumproduct nor SUMIFS and Pivot serve the purpose. Attached sample file . Hope you will be able to solve the crux. Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts Conditional Summation.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Conditional Sum or Sumproduct.
Attached sample file for the problem. Hope somebody among you will be able to solve the crux. Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts Conditional Summation.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Conditional Sum or Sumproduct.
Attached sample file for the problem. Hope somebody among you will be able to solve the crux. Regards, C.G.Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts Conditional Summation.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
