Can you post the spreadsheet, maybe with just the problem column? I tried in Excel 2000 without a problem. I was able to sum an entire column that met you specs, 0<x<1.
Posts by Seti


If you make a couple of changes in Maqbool's version of your spreadsheet, it may give you what you want. Extend the list of months down to include all of the months. Then change the numbers for each month to represent the 3rd argument in your VLOOKUPs so January would be 3, February would be 5, etc. Then try this formula:
=VLOOKUP(D2,VALUES!$A$11:$D$17,VLOOKUP(A2,$J$2:$K$13,2,0),FALSE)

Got it, sorry I missed this before. You have your parens in the wrong place. You had this:
=IF(AND(E18<>"B",E18<>"S"),"",IF(E18="B",G18F18,F18G18))/IF(D18="JPY",100,1)
when you wanted this:
=IF(AND(E18<>"B",E18<>"S"),"",IF(E18="B",G18F18,F18G18)/IF(D18="JPY",100,1))

I don't think that this is the prblem. When you divide 0 by anthing other than 0, you get 0. If it was a divide by 0 error, you would see #DIV/0! rather than #VALUE.
When you have a blank row, what does the AND portion of the formula equate to, TRUE or FALSE?

Does your formula require that every cell reference D5 and only D5? If so, you need to use absolute references like /IF($D$5="JPY",100,1). Also, in the previous examples, you had C12="JPY" not D5. If this was intended, then make sure that you are pointing to the correct cell.
If this is not the problem, what happens when you just compute the D5="JPY" part? Is this still an error or do you get TRUE or FALSE?

Does your formula require that every cell reference D5 and only D5? If so, you need to use absolute references like /IF($D$5="JPY",100,1).
If this is not the problem, what happens when you just compute the D5="JPY" part? Is this still an error or do you get TRUE or FALSE?

Are you comfortable with array formulas (entered usinf CTRL+SHIFT+ENTER)? If so, then you need your VLOOKUP formula to be entered over 2 or more columns. Here is a simple example that pulls the 2nd and 3rd column of a table and puts the results into 2 side by side cells.
=VLOOKUP(B7,$B$2:$D$4,{2,3},0)
In my example, I highlighted cells C7:D7 and entered this formula into one of the cells and then hit CTRL+SHIFT+ENTER and got the results you would hope for.
Seti

I am sorry, but I'm a little confused as to exactly where the error is coming from. My assumpiton is that you took Derk's formula and used it to replace yours. Then you copied this formula down a column. Now in some of the rows, columns E and C do have values and in some they don't. And in the ones that don't have values, you get the #VALUE error. Is this correct?
If my assumptions are correct, can you hit F2 to edit the formula and then hilight sections of it and press F9 to see which part of the formula is creating the error? For example, highlight this
AND(E12<>"B",E12<>"S")
Does this produce the error? If not try other portions of the formula.
Seti

#VALUE is an error message that means Excel could not calculate some part of the formula, usually because text was used rather than a number.
So in response to your post before last, are you saying that when you copy the formula down below row 12, you are getting the #VALUE error? What is in E13 when you copy down 1 row? What about C13? (If the problem is not on row 13, substitute your problem row instead).
Seti

How about a little different approach? If you change your conditional formatting to this:
=COUNTIF(A:A,A2)>1 and set the font to red
and copy down to all cells in A, it should change the font to red for all duplicates including the first occurrence of the duplicates.
The down side is using the entire A coulmn in the count.
To fix the spreadsheet you posted, your cell references are off by one starting in A3. They refer to the cell above the cell you are in. Change this and your configuration should work.

See what Tomach said about Application.ScreenUpdating = False
and Application.ScreenUpdating = True. These commands prevent the screen from updating while the macro is running so you won't see the sheets changing.