asked on

#value! in excel file- Cant find the cause

On formula tab starting at g131, I have a formula to complete the sum product. the formula works fine for some cells (G132, G133,G139,etc...) but not in g134-138. I believe the dimensions for the formulas are the same and i cant seem to find the root cause of the error.
Assignments--2-nov-2011.xlsm

SMP319

ASKER

also is there a way to complete using a named range for all the month transactions (i.e. create a range for Nov and use in each of the formulas

zorvek (Kevin Jones)

Your ranges are not compatible. They have to be the same size. For example, this formula in G134:

=SUMPRODUCT((Engineer=A$131)*Utilization!D$45:AG$65)

has two arrays that are not the same size. Engineer is 17 rows and Utilization!D$45:AG$65 is 21 rows. They have to be the same size.

Kevin

Member_2_6169280

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

Member_2_6169280

Check the following:
http://office.microsoft.com/en-us/excel-help/sumproduct-HP005209293.aspx

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SMP319

ASKER

Sorry about that. I was messing around with the name range of engineer and reduced the number of rows. I reset the number of rows to equal each other and it still returns the error. all the other rows have the same number of rows.

barry houdini

You could keep that as a SUMPRODUCT, Kevin, like this

=SUMPRODUCT((Engineer=A$131)*ISNUMBER(Utilization!C$47:F$63),Utilization!C$47:F$63)

regards, barry

SMP319

ASKER

This Worked. Thanks