Search:

Or try this shorter formula :





=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)

1] In excel, OR() is equal to " + " ( plus sign of arithmetic operator )

2] Check if a number is greater than or less than another number

=(E1<2.5) will return TRUE or FALSE

3] Thus, a...

But, the OP asked for : " How to return zero when computation in 100 / 0 ? ( or A1/A2 )"

I think the cell A1:A2 ( 100 / 0 ) were numbers

The ISERROR() definite not required

Regards
Bosco

Please refered to this Thread #12, regarding blank cell and formula blank cell

Blank Excel Cells

Regards
Bosco

Or try this,

can be used in all Excel versions and without ISERROR testing function :

=IF(A2,A1/A2,0)

Regards
Bosco

Hi Hans,

Oh!... my mistake!.....the mistake was corrected as per your advised.

Thank you for your good catch

Regards
Bosco

Herein the examples in explanation of using COUNT, COUNTA, COUNTBLANK and COUNTIF

Testing datas at Column A1:A5

......Col A...
..1....M......
..2....8......
..3.... ...... ( A3 is a blank...

Or, using only one IF function is enough

=IF(E2="","",(E2<2.5)+(E2>3.5))

Regards
Bosco

=IF(A2="ABC","ABC ","Others ")&IF(OR(G2={"A999","B999","C999","999A","999B","999C"}),"AUTO ",IF(OR(G2={"1 India","2 India","India 1","India 2","USA"}),"MANUAL...

Use " Text to Columns " way :

Select all column C "dates" >> Format Cell >> Number >> Date >> and choose : 2001-03-14 >> OK

>> Data >> Text to Columns >> Delimited >> Next >> Next >> choose :...

Or, try…………….

=LOOKUP(H2,A$2:D$61)

Regards
Bosco

Or, try to use this shorter formula :

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),)

Regards
Bosco

In follow Hans' table :

1] The Min value of each year ( Helper column ), H3 enter the array formula and copied down :

{=MIN(IF(--TEXT($C$3:$C$47,"yyyy")=G3,$D$3:$D$47))}

2] The cell address...

Or this non-array formula :

=SUMPRODUCT((Sheet1!B1:B10={"red","blue","green"})*Sheet1!A1:A10)

Regards
Bosco

Or, shorten to :

=IF((LEFT($C2)="S")+(($E2<TIME(8,,))+($E2>TIME(17,,))),B2,"")

Regards
Bosco

Another way, wihout using helper column,

Cell J8, enter the formula and copy down :

=SUMPRODUCT((F$2:F$349=I8)*(MATCH(F$2:F$349&A$2:A$349,F$2:F$349&A$2:A$349,)=ROW(F$2:F$349)-1))

Regards...

Try, F2……

=LEFT(TRIM(E2),FIND(" ",TRIM(E2)))

Regards
Bosco

I have list A and list B, some items in list A are the same as those in list B. How can I extract the ones that do not match?

Two (2) ways :

1] With helper column, Column E

1.1) Way A
...

=IF((E6="Yes")*(E8="Yes")*(E16="Yes"),"Accepted","Rejected")

Regards
Bosco

=IF(N(P35)*COUNTA($D$35:$O$35),P35/COUNTIF($D$35:$O$35,">0"),"")

Regards
Bosco

=TEXT(A1,"m")

Regards
Bosco

=SUMPRODUCT(ISTEXT(F10:F14)*(F10:F14<>""))

=COUNTIF(F10:F14,"?*")

Regards
Bosco

Pl see the attachment. Instead of entering formula for each sheet, is there a way to minimize the sumif with multiple sheet?

Sheet1
___|____A____]____B____|____C_____|____D____|...

I have don Adams in cell A3. I need a formula in cell B3 show me Don. and cell C3 show me Adams.

Another option :

B3 =MID(A3,1,FIND(" ",A3)-1)

C3 =MID(A3,FIND(" ",A3)+1,99)

Or

.........Although I have never used filters I though this would be a good way to sort and view my columns in C5:C1073 which it does but it will then not allow me to sum the revenue, turnover etc. for...