How to insert a cell reference into a sumproduct expression?

[Steve Rutkey]

Hello all,

My very first post so I'll try to do a good job of it. I've created a cost estimating spreadsheet that has come out pretty well, except for one deficiency I can't figure out:

In general terms, I have a cell (E9) that searches for a task (such as "01.01") and retrieves the row location of that task (for example, row 18). I then have a sumproduct expression that - as now written - relies on user editing the formula with the row # inserted. For example:

=SUMPRODUCT(--('Detailed Estimate'!$F$3:$J$3='REF Titles and Rates'!$B9),'Detailed Estimate'!$F$18:$J$18)

I would like to be able to paste this formula across numerous columns and have the formula self-update by inserting the correct row reference. For example, if next task is found in row 32, the formula would be the same except $F$18:$J$18 would change to $F$32:$J$32. I anticipate there's some way to use "indirect" expression but I'm at a loss.

Please see abbreviated version of my spreadsheet attached. Turning specifically to the attachment:

On the Detaile Estimate tab, the user selects tasks in the format xx.yy (such as 01.01, 02.03, etc.) that correspond to work assignments. The work is done by people w/ varying job titles (rates of pay); the user selects their titles from the pull-downs (green highlights).

The manager's estimating is done on the Detailed Estimate tab by entering hours for specific staff people (in the yellow highlighted areas). By copying and pasting the series of rows, additional tasks can be added. In reality, some projects might use very few tasks and some very many (up to about 60). {I gave up on trying to automate the addition or subtraction of tasks...but that's a separate matter }

Once the estimating is done on the Detailed Estimate tab, I need to be able to total hours in a different fashion: by job title. This is done on the REF Titles and Rates tab, in the blue highlighted areas. In Row 3, I have the spreadsheet retrieving a row identifier, and in columns E & F, I have manually entered the sumproduct formula to total hours by title, so as to illustrate where I'm trying to go. But how can I alter the column E/F formula such that it actively references the Row 3 data above...so I can then paste it across the remaining columns???

I look forward to your help. Thank you very much for any feedback.

-Steve

[Cutter]

Welcome to the forum.

If you really want SUMPRODUCT() then place this formula in E9 and copy down to E14 then drag that selected range to the right

=SUMPRODUCT(--('Detailed Estimate'!$F$3:$J$3='REF Titles and Rates'!$B9),INDEX('Detailed Estimate'!$F:$J,E$3,0))

However, SUMIF() is MUCH better option:

=SUMIF('Detailed Estimate'!$F$3:$J$3,'REF Titles and Rates'!$B9,INDEX('Detailed Estimate'!$F:$J,E$3,0))

[TMS]

Sorry, didn't look at the example properly ... I'll go away and, well, I'll just go away ;-(

[Cutter]

@TMShucks
Drinking again?

[TMS]

@Cutter: just a 1664 earlier on but it's late here and I think I should be in bed. My wife already thinks I'm very sad spending so much time "playing with Excel" as she describes it ;-)

[Cutter]

We may be married to the same woman!!!!

[moreeg]

I'm not sure if you are absolutely committed to using sumproduct. I think I have achieved what you need using SUMIF and INDIRECT. You can probably apply the INDIRECT to your SUMPRODUCT but it would be that much more complicated.

The one proviso is that you have the Worksheet set to Lotus Compatibility in the advanced settings. This needs to be un-ticked. If this is a deal breaker then some other solution besides INDIRECT will need to be used.

Let me know if this is close.

[Cutter]

@moreeg

I offered a SUMIF() and INDEX() solution in post #2. That would be preferable to the volatile INDIRECT() option.

[TMS]

@Cutter: so we may be related by marriage!? I'll call by when we visit Canada next year, all being well ... all being well that we visit Canada, that is.

Regards

[Cutter]

I look forward to it!
But, isn't the world supposed to end in October? Now that it didn't end last week!

[moreeg]

@Cutter - Apologies. I didn't read post #3 carefully enough - I thought that was you disavowing the post #2 solution. I wouldn't, as a strict rule, offer a solution when one has already been supplied - especially a better solution. I accept that INDEX is more efficient than INDIRECT. My solution is a reflection of my Excel expertise and, as indicated was not intended to one-up you. Again, my sincerest apologies.

[TMS]

@Cutter: If it happens, it happens ... we won't be coming ;-(

[Cutter]

@ moreeg
No apologies needed my friend. If you've got a better solution than what has been suggested you should certainly post it. I'm still learning and I'm always eager to see alternatives. I just wanted to point out to you my alternative to the SUMPRODUCT() because it appeared you may have missed it.

[Steve Rutkey]

Cutter and moreeg,

Sorry for my slow response - was away from the computer for a couple of days. But thank you very much. Cutter did in fact have two solutions right off the bat...and I agree that the SUMIF method works better. Moreeg's method works as well. I'll go with the SUMIF as it is concise and effective. I greatly appreciate your help!