Looking at the work you have already done and the detailed description, I
believe you only need to be pointed in the right direction...

Use INDIRECT function to build the dynamic reference you need.

To learn more about it, head to http://www.cpearson.com/excel/indirect.htm

For tons of great information at http://www.cpearson.com/excel/topic.aspx,
my thanks to Chip Pearson....

"Greg in CO" wrote:

Hi All!

I have a SUMPRODUCT formula that is working, but I would like to streamline
it from a maintenance point of view.

Here is the formula:

=SUMPRODUCT(--(Actuals!$B$2:$B$50="DepartmentA"),--(Actuals!$F$2:$F$50="Smith Company"),(Actuals!G$2:G$50))

This formula is in a cell in the January column( Column E), on a row (Row
7)titled "Smith Company Project" on a worksheet titled "DepartmentA".

The Actuals worksheet is populated by HR, listing hours for projects by
project name and by department.

The formula's logic is: Go to the Actuals sheet and lookup occurances of
"DepartmentA" and "Smith Company Project", then sum the corresponding hours
in E7.

The above formula works fine, but I have to go in and change the Department
reference and the Project reference for each depatment and project. There
will be many departments and many projects. As if that wasn't enough, after
HR populates the Actuals sheet, I have to go in and scrub the Projects info,
as there are many projects at the Smith Company, but all have different names
with "Smith Company" in them (Smith Company ERP, Finance Reporting - Smith
Co., Smith Co. Int'l, etc.). I have to change all those entries to "Smith
Company".

So, is there a modification where I can:

A - use an absolute cell reference for the Department value (i.e. the cell
on the Department sheet which has the Department's name) and for the Project
value (the cell with the row title for the project)?

B - have the formula look for entries where the entry may only contain a
partial match for the Project name?

Example: On the worksheet DepartmentA, cell C2 contains the Department's
name: "DepartmentA". Cell A7 contains the name of the overall project "Smith
Company".

I would like to use the cell ref "C2" instead of "DepartmentA" in the first
section of the formula and then an argument to look for entries containing
"Smith Company" in the second section of the formula. I have tried using the
cell ref for the Department and then either "*Smith Company*" or "*"&A7&"*"
for the Project references. They didn't work. However, when I typed the
exact names and adjusted the entries on the Actuals sheet, everything summed
just fine.

This modified formula also needs to be able to be "dragged" across the
columns for Feb-Dec.

Thank in advance for any assistance!!! :)
--
Greg