+ Reply to Thread
Results 1 to 5 of 5

Problem when using INDIRECT function in SUMIFS

  1. #1
    Registered User
    Join Date
    04-13-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    16

    Question Problem when using INDIRECT function in SUMIFS

    Hi Guys,

    I've been scratching my head over this one and the solution seems beyond my limited understanding of excel! Hoping someone can help.

    So i have been working on using INDIRECT functions so i can use dynamic worksheets in a data set, this all seemed to be working nicely and i managed to get the formulas working until i tried to drag the formula across the table date range and the sumifs sum range did not update as i dragged the formula across, instead it has remained locked to the original range see attached (B$3:B$4) i would like the sum range to move with the formula to C$3:C$4..D$3:D$4...E$3:E$4...F$3:F$4...etc etc if that makes sense.

    I have put together the attached to demonstrate the issue i am having.

    Hopefully this is possible would be a real life saver if i can get this to work!

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Problem when using INDIRECT function in SUMIFS

    Hi,

    Before you go too far with this can I suggest you are storing up trouble for yourself if you want to do any serious analysis with this data.


    You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

    A lot of people start by designing the forms that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

    You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
    You will also throw open the whole wonderful world of the powerful Pivot table functionality which will eliminate the need for formulae at all

    So before you get too far with this I'd create a single sheet database that contains the following columns

    Date
    Data Type - i.e. will contain the values data A, data B, Data C *
    Product Ref *
    Value

    The * values could be Validation drop down cells from which you could pick values.

    The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    04-13-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    16

    Re: Problem when using INDIRECT function in SUMIFS

    Hi Richard,

    Thanks for your reply, i do kind of see your point, however the main purpose of what i am using these data sets for is more tracking weekly data than it is doing any serious analysis, it does have a fairly straight forward function. The attached a very basic form i chucked together the is quite simplified and rebuilding everything that i have would take a huge amount of time i just dont have so i am looking for a short term work around at the moment, ultimately what im trying to achieve is not the correct approach but it is a work around that would help me if its possible.

    Does that make sense?

    Thanks for your help

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Problem when using INDIRECT function in SUMIFS

    Hi,

    The Indirect function is notoriously difficult to get your head around. Only use it if there is no other way. In this case enter the follwing in B3 of the Summary and copy across and down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If your data is consistent it shouldn't be too onerous to Copy your data from the daughter sheets and use Paste Special Transpose to turn them through 90 degrees and stack underneath each other on a new database sheet so that you have a proper table that a PT could use.

  5. #5
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Problem when using INDIRECT function in SUMIFS

    Indexing is one way to do it. However, if you have multiple sheets the formula will get increasingly complicated.
    If your data structure across all of your sheets is consistent you can use something like:

    Please Login or Register  to view this content.
    Copy it across and down. It will SUM all the same cells across all sheets between "data a" and "data c". If you can more sheets, just change the "data c" to the new last sheet.

+ 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. [SOLVED] Problem with sumifs function
    By Jonno1 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-05-2014, 06:41 AM
  2. [SOLVED] Problem with indirect function
    By zemigsan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-10-2013, 11:26 AM
  3. [SOLVED] Problem with Excel SUMIFS function
    By mbeurmann in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-04-2013, 04:45 PM
  4. [SOLVED] Cannot get INDIRECT function to work with SUMIFS
    By kborgers in forum Excel General
    Replies: 6
    Last Post: 11-22-2012, 11:21 AM
  5. Indirect function problem
    By shawnlss89 in forum Excel General
    Replies: 1
    Last Post: 05-26-2012, 02:36 AM
  6. Problem with Indirect Function
    By gth847x in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-23-2011, 12:38 PM
  7. Sumifs formula with Indirect function explanation
    By sqledge in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-19-2010, 01:01 AM
  8. INDIRECT Function problem
    By robhargreaves in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2007, 11:12 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