PDA

View Full Version : Solved: ok, what am i doing wrong in this one?



slang
08-23-2012, 07:28 AM
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

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

Bob Phillips
08-23-2012, 09:00 AM
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.

slang
08-23-2012, 09:33 AM
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?

Bob Phillips
08-24-2012, 02:24 AM
Yes, in my test most of the data was blanks.

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

Bob Phillips
08-25-2012, 02:03 AM
Don't coerce the sum range slang

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

Arismar
08-25-2012, 05:21 AM
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.

Bob Phillips
08-25-2012, 10:40 AM
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.

Arismar
08-25-2012, 06:10 PM
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.

Bob Phillips
08-26-2012, 01:09 AM
Whatever, believe what you believe.

slang
08-27-2012, 05:32 AM
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: