Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT....and then some!
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct IF Q | Excel Worksheet Functions | |||
Sumproduct #value! | Excel Worksheet Functions | |||
Sumproduct? | Excel Worksheet Functions |