+ Reply to Thread
Results 1 to 8 of 8

Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

  1. #1
    Registered User
    Join Date
    11-29-2012
    Location
    Kolkata
    MS-Off Ver
    Excel 2007
    Posts
    18

    Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    I have a sales Database as under:

    Date,Sales Person,Vendor,Sales Amount
    4/1/13,A,XYZ Ltd.,4500
    4/3/13,B,AAA Ltd.,3000
    4/3/13,B,BBB Ltd.,3000
    5/3/13,A,BBB Ltd.,2500
    6/1/13,B,AAA Ltd.,5000

    I want to extract data from the above database based on the selection of the month. If I select April' 13 it will show below:

    Date,Sales Person,Sales Amount
    4/1/13,A,4500
    4/3/13,B,6000 (Sum up sales amount happened in same date under a salesperson)

    Please help me and thanks in advance.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    Hi and welcome to the forum.

    Be sure that best way to describe your problem is to upload a sample workbook.

    Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.

    To attach a small sample workbook.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    Use a Pivot table grouped by months and use dates as report filter

  4. #4
    Registered User
    Join Date
    11-29-2012
    Location
    Kolkata
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    Yes, I can do it through Pivo table but I want to do it with formula.

  5. #5
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    You will never get the flexibility that you get with Pivot Tables, but "SUMIFS" will at least do what you ha asked for in your original note.

    Stephen

  6. #6
    Registered User
    Join Date
    11-29-2012
    Location
    Kolkata
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    I have a sales Database as under:

    Date,Sales Person,Vendor,Sales Amount
    4/1/13,A,XYZ Ltd.,4500
    4/3/13,B,AAA Ltd.,3000
    4/3/13,B,BBB Ltd.,3000
    5/3/13,A,BBB Ltd.,2500
    6/1/13,B,AAA Ltd.,5000

    I want to extract data from the above database based on the selection of the month. If I select April' 13 it will show below:

    Date,Sales Person,Sales Amount
    4/1/13,A,4500
    4/3/13,B,6000 (Sum up sales amount happened in same date under a salesperson)

    Please help me and thanks in advance.
    Attached Files Attached Files

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    In F19 and copy down

    =IF(MAX($F$18:$F18)=MAX($A:$A),"",SMALL($A:$A,1+COUNTIF($A:$A,"<="&N($F18))))

    In G19 and copy down.

    =INDEX(SP,MATCH(F19,Date,0))

    In H19 abd copy down.

    =SUMIFS(Transaction_Value,Date,F19,SP,G19)

  8. #8
    Registered User
    Join Date
    10-03-2012
    Location
    Denmark
    MS-Off Ver
    Excel 2016
    Posts
    38

    Re: Extract Monthly Data from a Sales Database (Sumup Sales Value Occurred in Same Date)

    See attached, using SUMIFS ...


    Stephen
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-27-2013, 05:56 PM
  2. [SOLVED] Sales report from sales data sheet if i select the date
    By loki7431 in forum Excel General
    Replies: 4
    Last Post: 02-06-2013, 09:43 PM
  3. Replies: 2
    Last Post: 06-19-2012, 10:19 AM
  4. summarize monthly sales from daily sales
    By top1 in forum Excel General
    Replies: 4
    Last Post: 01-05-2010, 11:59 PM
  5. copy week total in weekly sales worksheet to appropriate week in monthly sales
    By Sandy2976 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-15-2009, 01:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1