+ Reply to Thread
Results 1 to 11 of 11

Help with formula that filters through multiple sheets

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    Emeryville, CA
    MS-Off Ver
    Excel 2003/07/10
    Posts
    34

    Help with formula that filters through multiple sheets

    Hey all!

    I need a formula that filters through multiple sheets to tell me how much a person spent on an item (soda, etc) monthly. The reason there are multiple sheets is because they are different bank accounts that the same people use (ALL WORKSHEETS ARE SETUP THE SAME WAY). I know I have to use INDIRECT, SUMPRODUCT, and possibly SUMIF.
    I looked at some example of people using INDIRECT, but I am confused about the formula as a whole specifically date ranges. Please break down what each section means within the formula.

    So when I type in "Bill" in C2. it should populate from January through December what Bill bought that month. Like He bought $50.00 of soda in February and in December he bought $10.00.

    THANKYOU!

  2. #2
    Registered User
    Join Date
    05-19-2015
    Location
    Wesley Chapel, Florida
    MS-Off Ver
    2013
    Posts
    7

    Re: Help with formula that filters through multiple sheets

    Murphy -

    I've taken your information above and created the following spreadsheet with different worksheets as the Month ie Jan, Feb, ...Nov,Dec. If you define a named range for "Names" then use Data Validation to select from a List of the Names - this enables a value in C2. When the drop down changes it will automatically retrieve the data from the Month Worksheets. Utilizing the Vlookup(cell reference (C2),Indirect(Concatenate(D$1,"!","$A$2:$D$5"),True),2,False) - this enabled us to use the header row to populate the remainder data based on worksheet tab.

    Review and advise

    Christopher Shay

    Filter through Multiple Sheets.xlsx
    Last edited by ChristopherShay; 05-19-2015 at 11:32 PM. Reason: Moving position of file url

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    Re: Help with formula that filters through multiple sheets

    This will do what you want.

    1. create a list of all your worksheets
    2. Give that list a range name (say, accounts)
    3. Use this formula, adjusted for your ranges and criteria...
    =SUMPRODUCT(SUMIF(INDIRECT("'"&Accounts&"'!T6:T1000"),C794,INDIRECT("'"&Accounts&"'!M6:M1000")))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    07-31-2013
    Location
    Emeryville, CA
    MS-Off Ver
    Excel 2003/07/10
    Posts
    34

    Re: Help with formula that filters through multiple sheets

    Christopher: I like the setup that you did, but I want the worksheets to be by account not by month. So it's the total of Bill's spending on a certain item throughout the accounts.

    Ford: is C749 where I put the criteria? and if so how do i input the date ranges to say "<=" or ">=" a certain date.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    Re: Help with formula that filters through multiple sheets

    is C749 where I put the criteria?
    Yes, and you can adjust this reference as needed.

    If you cannot adjust the formula to suite your needs, please upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

  6. #6
    Registered User
    Join Date
    07-31-2013
    Location
    Emeryville, CA
    MS-Off Ver
    Excel 2003/07/10
    Posts
    34

    Re: Help with formula that filters through multiple sheets

    This is how I want it to work. On the first sheet (Summary) when I enter in person's name on F1, The formulas in the chart should gather all of the information in sheets called "Account 1" and
    "Account 2" relating to Bill. Bill used both accounts and so the the information should be by month and total of each category.

    Your equation works, I just need to figure out how to do the date range criteria for C794 in your equation
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    564

    Re: Help with formula that filters through multiple sheets

    On sheet Account add 1 more column with this formula:
    Please Login or Register  to view this content.
    So in your formula you can use that column as criteria_range for month, and use sumifs formula.
    Click (*) if you received helpful response.

    Regards,
    David

  8. #8
    Registered User
    Join Date
    07-31-2013
    Location
    Emeryville, CA
    MS-Off Ver
    Excel 2003/07/10
    Posts
    34

    Re: Help with formula that filters through multiple sheets

    what does the formula do? is it renaming the ranges?

  9. #9
    Registered User
    Join Date
    07-31-2013
    Location
    Emeryville, CA
    MS-Off Ver
    Excel 2003/07/10
    Posts
    34

    Re: Help with formula that filters through multiple sheets

    @FDippins or anyone else I have come up with this formula, but I only get 0 as the answer

    =SUMPRODUCT((SUMIF(INDIRECT("'"&Accounts&"'!I3:I1000"),$F$1,INDIRECT("'"&Accounts&"'!BC3:BC1000")))*(SUMIF(INDIRECT("'"&Accounts&"'!C3:C1000"),"<=1/1/2015",INDIRECT("'"&Accounts&"'!BC3:BC1000")))*(SUMIF(INDIRECT("'"&Accounts&"'!C3:C1000"),"<=1/31/2015",INDIRECT("'"&Accounts&"'!BC3:BC1000"))))

    Column I = The persons name (which will be entered in F1)
    Column BC = The Money range
    Column C = The dates


    Am I close???

  10. #10
    Registered User
    Join Date
    07-31-2013
    Location
    Emeryville, CA
    MS-Off Ver
    Excel 2003/07/10
    Posts
    34

    Re: Help with formula that filters through multiple sheets

    I have figured it out!!! Posting my formula so other people can hopefully find it useful

    I just need to use the SUMIFS Formula with the INDIRECT and that helps with my multiple criteria when having date ranges. NO SUMPRODUCT!
    D Column= Money totals
    I column = Name which is entered into F1
    C Column = Date
    S Column = the actual date (1/1/2015, 1/31/2015) need "&" before the date

    =SUMIFS(INDIRECT("'"&Accounts&"'!D3:D1000"),INDIRECT("'"&Accounts&"'!I3:I1000"),$F$1,INDIRECT("'"&Accounts&"'!C3:C1000"),">="&S2,INDIRECT("'"&Accounts&"'!D3:D1000"),"<="&S3)

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with formula that filters through multiple sheets

    If you're getting a correct result with that formula it's just a coincidence.

    It has to be processed as an array using either the normally entered SUMPRODUCT function:

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&Accounts&"'!D3:D1000"),INDIRECT("'"&Accounts&"'!I3:I1000"),$F$1,INDIRECT("'"&Accounts&"'!C3:C1000"),">="&S2,INDIRECT("'"&Accounts&"'!D3:D1000"),"<="&S3))

    Or array entered** using the SUM function:

    =SUM(SUMIFS(INDIRECT("'"&Accounts&"'!D3:D1000"),INDIRECT("'"&Accounts&"'!I3:I1000"),$F$1,INDIRECT("'"&Accounts&"'!C3:C1000"),">="&S2,INDIRECT("'"&Accounts&"'!D3:D1000"),"<="&S3))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Dynamic filters (different strings) for multiple sheets in the same workbook
    By wildradical in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-03-2014, 08:30 AM
  2. [SOLVED] How To... array formula that filters by multiple criteria on same column
    By jprealini in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2014, 11:30 AM
  3. Replies: 1
    Last Post: 02-12-2014, 01:49 AM
  4. Replies: 4
    Last Post: 07-24-2012, 01:21 PM
  5. [SOLVED] Turn filters on in multiple sheets
    By EstherJ in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-05-2005, 01:05 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