We help IT Professionals succeed at work.

#value! in excel file- Cant find the cause

SMP319
SMP319 asked
on
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
Comment
Watch Question

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2008

Commented:
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

Commented:
The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
CERTIFIED EXPERT
Top Expert 2008
Commented:
Here is a formula that should produce better results in cell G134:

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

Enter as an array formula by pressing CTRL+SHIFT+ENTER.

Kevin

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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

Author

Commented:
This Worked. Thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.