PDA

View Full Version : Solved: Formula Question



vzachin
09-02-2006, 08:40 PM
Hi,

Is there a better way with what i'm doing?
Currently, I have data in 2 columns beginning in E5 & F5 with values of either "N" or "Y". I want to know the total count of any change in value, from "N" to "Y" or "Y" to "N".
If there is only one row of data in E5 & F5, this fails.


Range("G5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-1],0,1)"
Range("G5:G" & Cells(Rows.Count, "F").End(xlUp).Row).FillDown
Application.CutCopyMode = False
Range("G4").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[65532]C)"
Range("G4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G5:G5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("G5").Select



there must be an easier way
thanks
zach

Bob Phillips
09-03-2006, 05:10 AM
Just use

=SUMPRODUCT(--(E5:E1000<>F5:F1000))

vzachin
09-03-2006, 01:04 PM
xld,

that's so cool. thanks!

zach