SUMPRODUCT....and then some!

[Greg in CO]

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!!!

 

[Sheeloo]

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

 

[Roger Govier]

Hi Greg

it sounds as though you would benefit from using a pivot table for your
report.
For information on setting up Pivot Tables, take a look at Debra Dalgleish's
site
http://www.contextures.com/tiptech.html
scroll down to the section on Pivot Tables
or
Mike Alexander's video tutorials at
http://www.datapigtechnologies.com/ExcelMain.htm

If you need more help on PT's post back

 

[Greg in CO]

Thanks Sheeloo and Roger...both options would be great, however:

- With Pivot tables, my end users go fetal and hide under their desks

- For the Indirect, wouldn't the cell reference on the Indirect-referenced
worksheet be "locked" in the formula, thus preventing me from "dragging" the
formula across the remaining columns or pasting it into rows for other
projects?

There will be several departments and many, many referenced projects. I've
used INDIRECT before, but only to make references to other worksheets dynamic.

Is there way to replace the "DepartmentA" reference with a reference to the
current sheet? I am guessing that any cell reference within the
SUMPRODUCT(--(X)) [X=cell ref location in the formula] would refer to that
cell on the sheet referenced in the formula and not on the current sheet.

I will look at the websites you have provided in the meantime.

Thanks so much for your patience....I feel i am almost there in getting this
streamlined and it is the last little chunk that is frustrating.

Greg

 

[Sheeloo]

You can manipulate the formula/reference by using a combination of
absolute/relative references or populating the addresses in a column and then
use INDIRECT... There are many ways you can achieve your requirement.

What I normally do is use a cell on the sheet to hold the Sheet name and
refer to that so that even if a copy is created I don't have to change
anything... INDIRECT and VLOOKUP in combination are very effective.

Any reference will refer to the address you get after replacing all formulae
with thier results...

Thanks Sheeloo and Roger...both options would be great, however:

- With Pivot tables, my end users go fetal and hide under their desks

- For the Indirect, wouldn't the cell reference on the Indirect-referenced
worksheet be "locked" in the formula, thus preventing me from "dragging" the
formula across the remaining columns or pasting it into rows for other
projects?

There will be several departments and many, many referenced projects. I've
used INDIRECT before, but only to make references to other worksheets dynamic.

Is there way to replace the "DepartmentA" reference with a reference to the
current sheet? I am guessing that any cell reference within the
SUMPRODUCT(--(X)) [X=cell ref location in the formula] would refer to that
cell on the sheet referenced in the formula and not on the current sheet.

I will look at the websites you have provided in the meantime.

Thanks so much for your patience....I feel i am almost there in getting this
streamlined and it is the last little chunk that is frustrating.

Greg

 

[Roger Govier]

Hi Greg

PT's aren't that frightening<bg>
However if you wish to persist with Sumproduct, then with Department
required in A7 and Company in C2

=SUMPRODUCT(--(Actuals!$B$2:$B$50=C$2),
--(ISNUMBER(FIND($A7,Actuals!$F$2:$F$50))),
(Actuals!G$2:G$50))

Note you will have to put Smith Co in A7 if you want to pick up all the
variations you gave.

I would also prefer to create some named ranges for the columns on Actual,
which are dynamic and will grow with the amount of data entered.
Insert>name>Define> Name Departments Refers to
=Actuals!$B$2:INDEX(Actuals!$B:$B,COUNTA(Actuals!$B:$B)-1)

Repeat for
Companies with column F
=Actuals!$F$2:INDEX(Actuals!$F:$F,COUNTA(Actuals!$B:$B)-1)
and
Hours with Column G
=Actuals!$G$2:INDEX(Actuals!$G:$G,COUNTA(Actuals!$B:$B)-1)

Note column B is being used for the count in all cases to ensure the ranges
are of the same length for the Sumproduct formula.

The formula then reads
=SUMPRODUCT(--(Department=C$2),
--(ISNUMBER(FIND($A7,Companies))),
(Hours))

Much easier to read and maintain
--
Regards
Roger Govier

Thanks Sheeloo and Roger...both options would be great, however:

- With Pivot tables, my end users go fetal and hide under their desks

- For the Indirect, wouldn't the cell reference on the Indirect-referenced
worksheet be "locked" in the formula, thus preventing me from "dragging"
the
formula across the remaining columns or pasting it into rows for other
projects?

There will be several departments and many, many referenced projects.
I've
used INDIRECT before, but only to make references to other worksheets
dynamic.

Is there way to replace the "DepartmentA" reference with a reference to
the
current sheet? I am guessing that any cell reference within the
SUMPRODUCT(--(X)) [X=cell ref location in the formula] would refer to that
cell on the sheet referenced in the formula and not on the current sheet.

I will look at the websites you have provided in the meantime.

Thanks so much for your patience....I feel i am almost there in getting
this
streamlined and it is the last little chunk that is frustrating.

Greg

 

[Greg in CO]

Thanks Roger/Sheloo! I'll give this a try. I am trying to keep the workbook
at a level where the users only enter numbers, thus eliminating typos, etc.
It's been a challenge.
--
Greg

Hi Greg

PT's aren't that frightening<bg>
However if you wish to persist with Sumproduct, then with Department
required in A7 and Company in C2

=SUMPRODUCT(--(Actuals!$B$2:$B$50=C$2),
--(ISNUMBER(FIND($A7,Actuals!$F$2:$F$50))),
(Actuals!G$2:G$50))

Note you will have to put Smith Co in A7 if you want to pick up all the
variations you gave.

I would also prefer to create some named ranges for the columns on Actual,
which are dynamic and will grow with the amount of data entered.
Insert>name>Define> Name Departments Refers to
=Actuals!$B$2:INDEX(Actuals!$B:$B,COUNTA(Actuals!$B:$B)-1)

Repeat for
Companies with column F
=Actuals!$F$2:INDEX(Actuals!$F:$F,COUNTA(Actuals!$B:$B)-1)
and
Hours with Column G
=Actuals!$G$2:INDEX(Actuals!$G:$G,COUNTA(Actuals!$B:$B)-1)

Note column B is being used for the count in all cases to ensure the ranges
are of the same length for the Sumproduct formula.

The formula then reads
=SUMPRODUCT(--(Department=C$2),
--(ISNUMBER(FIND($A7,Companies))),
(Hours))

Much easier to read and maintain
--
Regards
Roger Govier

Thanks Sheeloo and Roger...both options would be great, however:

- With Pivot tables, my end users go fetal and hide under their desks

- For the Indirect, wouldn't the cell reference on the Indirect-referenced
worksheet be "locked" in the formula, thus preventing me from "dragging"
the
formula across the remaining columns or pasting it into rows for other
projects?

There will be several departments and many, many referenced projects.
I've
used INDIRECT before, but only to make references to other worksheets
dynamic.

Is there way to replace the "DepartmentA" reference with a reference to
the
current sheet? I am guessing that any cell reference within the
SUMPRODUCT(--(X)) [X=cell ref location in the formula] would refer to that
cell on the sheet referenced in the formula and not on the current sheet.

I will look at the websites you have provided in the meantime.

Thanks so much for your patience....I feel i am almost there in getting
this
streamlined and it is the last little chunk that is frustrating.

Greg
--
Greg

Hi Greg

it sounds as though you would benefit from using a pivot table for your
report.
For information on setting up Pivot Tables, take a look at Debra
Dalgleish's
site
http://www.contextures.com/tiptech.html
scroll down to the section on Pivot Tables
or
Mike Alexander's video tutorials at
http://www.datapigtechnologies.com/ExcelMain.htm

If you need more help on PT's post back
--
Regards
Roger Govier

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!!!