• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

=SUMPRODUCT not working

B

Bob G.

Member
I comparing my Actual Duration (AD) to my Original Duration (OD) using "SUMPRODUCT". The issues i am have is it works in version #1 on all my workbooks, but in Version #2 i am only able to get it work in one workbook.

Version #1
=SUMPRODUCT((ALL_Data[PCT]=1)*(ALL_Data[MAIN]="P")*(ALL_Data[Update]=US)*(ALL_Data[AD]>ALL_Data[OD])) this returns 107

Version #2
=SUMPRODUCT((ALL_Data[PCT]=1)*(ALL_Data[MAIN]="P")*(ALL_Data[Update]=US_2)*(ALL_Data[AD]>ALL_Data[OD])*((ALL_Data[AD]-ALL_Data[OD])>20)) this returns #NAME?

Delta between #1 and #2 is the adding AD-OD>20. As i noted above i have it work on one of my other projects. Can anyone tell me why it works in one workbook and not another (also, the workbook that is not working is a copy of the workbook that is working). Thanks for any and all help.
 
Hi Bob ,

Do you mean to say Version #2 of your formula works in one file and doesn't in another ?

If so , is US_2 a named range ? Is it defined in the file where the formula doesn't work ?

Narayan
 
I noticed that you have a reference to US in the first formula but US_2 in the second formula.
 
Sorry my bad. The name range "US" and "US_2" are the same (use two different location to copy from)...
 
Hi Bob ,

The formula which you posted in your first post - is it copied from the file where it worked or from the file where it did not work ?

Narayan
 
Narayan,

The formula are the same in both worksheets. V#1 works in both, but, V#2 only works in one of the two worksheet. There are no difference other then the project data the formula is pulling from....
 
Hi Bob ,

In the file where it does not work , why don't you try splitting it up ?

First try one part of it as follows :

=SUMPRODUCT((ALL_Data[PCT]=1)*(ALL_Data[MAIN]="P")*(ALL_Data[Update]=US_2))

Next try the following :

=SUMPRODUCT((ALL_Data[AD]>ALL_Data[OD])*((ALL_Data[AD]-ALL_Data[OD])>20))

See which of them gives an error.

Narayan
 
Naraya,
=SUMPRODUCT((ALL_Data[PCT]=1)*(ALL_Data[MAIN]="P")*(ALL_Data[Update]=US_2)) returned 138
=SUMPRODUCT((ALL_Data[AD]>ALL_Data[OD])*((ALL_Data[AD]-ALL_Data[OD])>20)) returned error
=SUMPRODUCT((ALL_Data[AD]>ALL_Data[OD])) returned 0 (????????)
=SUMPRODUCT(((ALL_Data[AD]-ALL_Data[OD])>20)) returned error
 
Hi Bob ,

=SUMPRODUCT(((ALL_Data[AD]-ALL_Data[OD])>20))

should be a combination of FALSE and TRUE values ; FALSE being the same as 0 , and TRUE being the same as 1 , you should get a value.

Narayan
 
One more observation...
Since you are getting errors when you have a subtraction, please check whether you have non-numeric values in ALL_Data[AD] or ALL_Data[OD]
 
Sajan,
Thank you, that was my issue. I added some "Hammock's" to by new project and it added a "*" to durations for hammock. Forgot to remove them before importing the data.... Thanks you again...
 
You must log in or register to reply here.
Back
Top