# Sumproduct_sumif_indirect formulas

1. ## 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.

2. ## Re: Sumproduct_sumif_indirect formulas

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

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

3. ## 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. ## 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. ## Re: Sumproduct_sumif_indirect formulas

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

Cheers

6. ## Re: Sumproduct_sumif_indirect formulas

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

Cheers

7. ## 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. ## Re: Sumproduct_sumif_indirect formulas

Originally Posted by qdavq
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. ## 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?

qdavq

10. ## Re: Sumproduct_sumif_indirect formulas

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

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