Ok, what am i doing wrong in this one?

Printable View

08-23-2012, 07:28 AM
slang

Ok, what am i doing wrong in this one?

Been a while so I am rusty once again.
Want to sum data in dynamic range "profiledata" col 47.
If it meets the three criteria in the formula.
1st matches a number in col 16 with one in B8.
2nd matches a date greater than the date in $e$e
3rd matches a date less than the date in $d$4

Code:

=SUMPRODUCT(--(INDEX(profiledata,,16)=B8),--(INDEX(profiledata,,46)>$E$4),--(INDEX(profiledata,,46)<$D$4),--(INDEX(profiledata,,47)))

Do I need an OR in there somewhere as I just get #VALUE! as a result even if entered as an array formula.

Please help out a rusty fellow:dunno :banghead:
08-23-2012, 09:00 AM
Bob Phillips

It worked fine for me with some data I setup, it certainly does not need array-entering.

Can you post the offending workbook, my guess the problem is with the definition of profiledata.
08-23-2012, 09:33 AM
slang

Quote:

Originally Posted by xld

It worked fine for me with some data I setup, it certainly does not need array-entering.

Can you post the offending workbook, my guess the problem is with the definition of profiledata.

Quick question, can the data in the col have blanks?
08-24-2012, 02:24 AM
Bob Phillips

Yes, in my test most of the data was blanks.
08-24-2012, 05:41 AM
slang

1 Attachment(s)

Quote:

Originally Posted by xld

Yes, in my test most of the data was blanks.

Here is a cut of the sheet.
08-25-2012, 02:03 AM
Bob Phillips

Don't coerce the sum range slang

Code:

=SUMPRODUCT(--(INDEX(profiledata,,46)>=D17),--(INDEX(profiledata,,46)<=E17),--(INDEX(profiledata,,16)=E19),INDEX(profiledata,,47))
08-25-2012, 05:21 AM
Arismar

Set profiledata to $A$2:$FL$13. The mistake is to include the line containing the column names. In this case the function SUMPRODUCT will include cells containing the values of the first row, the row containing the column names, resulting in an error. Because the value of it is invalid to the function.
08-25-2012, 10:40 AM
Bob Phillips

Quote:

Originally Posted by Arismar

Set profiledata to $A$2:$FL$13. The mistake is to include the line containing the column names. In this case the function SUMPRODUCT will include cells containing the values of the first row, the row containing the column names, resulting in an error. Because the value of it is invalid to the function.

No, you are wrong there. If he had been using the * operator then he would need to avoid the column headers, but as he is using -- there is no need.

His problem was that he was also using the -- on the range to be summed, --(INDEX(profiledata,,47), and as there were blanks it there it tried to coerce a space to a numeric value, hence the #VALUE.
08-25-2012, 06:10 PM
Arismar

Quote:

Originally Posted by xld

No, you are wrong there. If he had been using the * operator then he would need to avoid the column headers, but as he is using -- there is no need.

His problem was that he was also using the -- on the range to be summed, --(INDEX(profiledata,,47), and as there were blanks it there it tried to coerce a space to a numeric value, hence the #VALUE.

Yes. I think you are wrong there. The problem occurs because - (INDEX (profiledata,, 47)> = D17 will go through the entire column 47 from the first line to the last. And the error will occur when the function will try to compare the first cell with the cell D17. Try redefine profiledata as I said earlier. I tested it and worked.
08-26-2012, 01:09 AM
Bob Phillips

Whatever, believe what you believe.
08-27-2012, 05:32 AM
slang

Quote:

Originally Posted by xld

Don't coerce the sum range slang

=SUMPRODUCT(--(INDEX(profiledata,,46)>=D17),--(INDEX(profiledata,,46)<=E17),--(INDEX(profiledata,,16)=E19),INDEX(profiledata,,47))

Excellent, works perfect!
I knew there was something small wrong in there somewhere.
You are the lord :bow: :friends: