Search This Blog

Wednesday 20 June 2012

COUNT UNIQUE ENTRIES FROM A LIST OF DUPLICATE DATA


Formula in B2:

=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))

FORMULA TO SUM ALL ODD NUMBERS BETWEEN 1 TO 100

Formulas:

=SUM(2*ROW(OFFSET($A$1,,,100/2))-1) 

Press Ctrl+Shft+Entr

=SUM(ROW(1:100)*MOD(ROW(1:100),2))

Press Ctrl+Shft+Entr

=SUM(ROW(1:100)*ISODD(ROW(1:100)))

Press Ctrl+Shft+Entr

All these formulas can be used for the same purpose

FORMULA TO SUM ALL EVEN NUMBERS BETWEEN 1 TO 100

 Array Formula:

=SUM(ROW(1:100)*ISEVEN(ROW(1:100)))

Press   Ctrl+Shft+Entr.

ADD NUMBERS HAVING TEXT WITH IT


3 Formulas to do this

Formula1 in B2:  =SUMPRODUCT(--SUBSTITUTE(UPPER(G33:G37),"K",""))
Formula2 in B3:  =SUM(--LEFT(G33:G37,LEN(G33:G37)-1))        Press Ctrl+Shft+Enter
Formula3 in B4:  =SUMPRODUCT(--(LEFT(G33:G37,LEN(G33:G37)-1)))    Press Ctrl+Shft+Enter

EXTRACT NUMBERS FROM AN ALPHANUMERIC STRING WHEN NUMBERS ARE NOT CLUSTERED


Alphanumeric StringNumbers
RED458ELT94589
s324rake8ete3248
drk77dFTT563j77563
FKD5RR5
4tryr3514351
3fdffd4f5345
Rkknk4656s4656
9c5vcv933`90923]]9593390923
À87&‰ð$ 45§§Ø7çz24[8745724
~9¯œ»~ô855‹eg87Û©3798558737



Array Formula:

=NPV(-0.9,,IFERROR(MID(A58,LEN(A58)-ROW(INDIRECT("1:"&LEN(A58)))+1,1)%,""))

Press Ctrl+Shft+Enter