+ Reply to Thread
Results 1 to 10 of 10

Sumproduct_sumif_indirect formulas

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2010
    Posts
    9

    Sumproduct_sumif_indirect formulas

    I am working on a Macbook pro using Microsoft excel for Mac 2011.

    Hi there,

    I have developed a simple construction estimating template consisting of a number of sheets.

    The basic sheets are:
    (1) A submission schedule on which I list the item number, as short description and the quantity required for the item. Each item line to its relevant pricing sheet (3) and brings forward the calculated rates. (I have this sheet working)
    (2) A resources sheet in which all the resources required are listed and classified as materials, labour, plant etc etc etc
    (3) Price build up sheets for each item listed in (1) above. I use data validation lists and vlookup formulas to bring data front the resources sheet and all works well. I have used a VBA formula that automatically names the sheet after the item number being prices. (I have got this sheet working well)

    I have inserted tables into all the sheets and the pricing sheets has 3 tables namely,
    My issue is that I would like to summarise each resource usage in the resources sheet.

    When I use the following formula with specific cell references the resource usage calculates fine.

    =SUMPRODUCT(SUMIF(INDIRECT("'"&table_item&"'!$A$34:$A$39"),A2,INDIRECT("'"&table_item&"'!$d$34:$d$39")))
    the formula works fine while i only work with in the range of row 34 to 39. This is however not always the case as some item will need 3 rows to price and other many more.

    I have tried the following formula using table and named ranges
    =SUMPRODUCT(SUMIF(INDIRECT("'"&table_item&"'!Res_Description_Mats"),A2,INDIRECT("'"&table_item&"'!Res_Qty_Mats")))
    the problem is however the formula on uses the qty's in the first tab and multiplies (or adds) the quantity by the number of items there are.

    References
    table_item = submission schedule (1) A24:A29
    Res_Description_Mats = first pricing sheet (3) A33:A39
    Res_Qty_Mats = first pricing sheet (3) D34:D39

    The question is how do I get the second formula to look at all the pricing tabs lists in the "table_item"

    I need the resources to summarise to use in the budget tab.

    I have attached the file.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: Sumproduct_sumif_indirect formulas

    Change your workbook level named range Res_Amount_Mats refers to with the below text.

    ="$A$34:$A$39"


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    03-01-2013
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Sumproduct_sumif_indirect formulas

    Hi Sixthsence, thanks for the reply.

    The issue that I have with the ="$A$34:$A$39"option is that $A$39 will in most cases "grow" past line 39. Is there anyway I can have a dynamic Res_Amount_Mats type reference that will automatically include an increase in the range.

    Thanks

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: Sumproduct_sumif_indirect formulas

    This will take care of the auto Increment issue.

    ="$A$34:$A$"&ROW(Table5[[#All],[Materials Total]])+ROWS(Table5[[#All],[Materials Total]])-1

  5. #5
    Registered User
    Join Date
    03-01-2013
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Sumproduct_sumif_indirect formulas

    Sixthsence. Thanks I will give that a go and leet you know.

    Cheers

  6. #6
    Registered User
    Join Date
    03-01-2013
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Sumproduct_sumif_indirect formulas

    Sixthsence. Thanks I will give that a go and leet you know.

    Cheers

  7. #7
    Registered User
    Join Date
    03-01-2013
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Sumproduct_sumif_indirect formulas

    Hi Sixthsence

    Your proposal works, however if i go to tab 3 and add more lines the items added below the original table size does not carry forward.

    Have you got any magic up your sleeve to help solve this one?

    Thanks

    qdavq

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: Sumproduct_sumif_indirect formulas

    Quote Originally Posted by qdavq View Post
    Your proposal works, however if i go to tab 3 and add more lines the items added below the original table size does not carry forward
    Because it's Table5273 and not Table5

  9. #9
    Registered User
    Join Date
    03-01-2013
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Sumproduct_sumif_indirect formulas

    Sixthsence

    Is there anyway a person could set up a formula that if any related table 5 or 52375 etc etc change in size the formula will pick up these changes? i.e. tab 1 remains the same but on tab 3 the tab 3 table 5 is increase?

    Thank you in advance.

    qdavq

  10. #10
    Registered User
    Join Date
    03-01-2013
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Sumproduct_sumif_indirect formulas

    Thanks Sixthsence. I have now solved this problem. Your help is greatly appreciated and made life much easier

+ 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. Using Cell references in file paths for formulas to create dynamic formulas
    By MichaelStokesJr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2013, 11:49 AM
  2. Replies: 5
    Last Post: 09-25-2013, 02:51 PM
  3. Ctrl+Shift+Enter (CSE) array formulas in VBA using differing formulas
    By officeguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2012, 04:31 PM
  4. Help modifying macro to wrap iferror formulas around existing formulas
    By dcgrove in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2012, 02:04 PM
  5. Replies: 5
    Last Post: 05-05-2008, 02:22 PM

Tags for this Thread

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