+ Reply to Thread
Results 1 to 3 of 3

Sumif function using indirect function and data from different sheet

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    2

    Sumif function using indirect function and data from different sheet

    Hi there from a new user,

    I have a spreadsheet with two sheets. The one (called Deb) with all outstanding invoices with ao column G "due date" and column I "invoice amount". The second sheet (called Timeline) gives an overview of the total of the outstanding amounts per due date. Here the due dates are below each other in rows. Before I have been using the formula =SUMIF(Deb!G$3:G$148;O5;Deb!I$3:I$148) where O5 gives the relevant due date. But since the data in "Deb" is replaced every week G$148 and I$148 are not static. Now I want to add the indirect function to make it flexible.

    The idea is to use cell P1 and Q1 in tab Timeline to fill in the relevant numbers for Column G (in the example 3 and 148)

    I have tried the following formula but it does not work:

    =SUMIF(INDIRECT("'Deb'!G"&P1&":G"&Q1;O5;"'Deb'!I"&P1&":I"&Q1))

    Any ideas how to do this correct?

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Sumif function using indirect function and data from different sheet

    hi pronky007, welcome to the forum. you need 2 INDIRECT. 1 for the range, 1 for sum_range.
    =SUMIF(INDIRECT("Deb!G"&P1&":G"&Q1);O5;INDIRECT("Deb!I"&P1&":I"&Q1))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    10-16-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Sumif function using indirect function and data from different sheet

    Thanks a bunch, works perfectly!

    Egbert

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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