Slight change of formula

[ExcelRoy]

Hi guys,

i need a slight change to my formula

=SUM(INDEX(Q15:Q138,MATCH("*Additions*",H15:H138,0)):Q138)

at the start of the sheet if nothing is entered it shows "#N/A" but i would like it to say "---"

Hope this makes sense

Thanks

 

[Aladin Akyurek]

Hi guys,

i need a slight change to my formula

=SUM(INDEX(Q15:Q138,MATCH("*Additions*",H15:H138,0)):Q138)

at the start of the sheet if nothing is entered it shows "#N/A" but i would like it to say "---"

Hope this makes sense

Thanks

On 2007 or later...

=IFERROR(SUM(INDEX(Q15:Q138,MATCH("*Additions*",H15:H138,0)):Q138),"---")

Otherwise:

=IF(ISNUMBER(MATCH("*Additions*",H15:H138,0)),
    SUM(INDEX(Q15:Q138,MATCH("*Additions*",H15:H138,0)):Q138),
    "---")

 

[John Davis]

Hi guys,

i need a slight change to my formula

=SUM(INDEX(Q15:Q138,MATCH("*Additions*",H15:H138,0)):Q138)

at the start of the sheet if nothing is entered it shows "#N/A" but i would like it to say "---"

Hope this makes sense

Thanks

Does this work?

=IF(ISERROR(SUM(INDEX(Q15:Q138,MATCH("*Additions*",H15:H138,0)):Q138)),"---",SUM(INDEX(Q15:Q138,MATCH("*Additions*",H15:H138,0)):Q138))

 

[ExcelRoy]

Fantastic thanks Aladin, sorry John didnt see your solution at first

what about the same with

=SUM(T142-Q142)/T142

Thanks

 

[Aladin Akyurek]

Fantastic thanks Aladin, sorry John didnt see your solution at first

what about the same with

=SUM(T142-Q142)/T142

Thanks

Either...

=IF(N(T142),(T142-Q142)/T142,"")

Or...

=IF(N(T142),(T142-Q142)/T142,0)

 

[ExcelRoy]

Thanks Aladin,

Forgot to mention that this cell is a percentage

so if those cells are showing " " can this cell show " "

Thanks

 

[Aladin Akyurek]

Thanks Aladin,

Forgot to mention that this cell is a percentage

so if those cells are showing " " can this cell show " "

Thanks

Try...

=IF(N(T142), 
    IF(N(Q142), 
      (T142-Q142)/T142,
      ""),
    "")

Note. Not " ", which is a space char, rather "".

 

[ExcelRoy]

Hi Aladin,

I have a real simple one

=SUM(Q146-Q142)

shows up #VALUE

How do i have that say "" instead of #VALUE

Thanks

 

[Aladin Akyurek]

Hi Aladin,

I have a real simple one

=SUM(Q146-Q142)

shows up #VALUE

How do i have that say "" instead of #VALUE

Thanks

Try...

=IF(ISNUMBER(Q146-Q142),Q146-Q142,"")

 

[ExcelRoy]

Many many thanks Aladin,

Just one more tho if you would be so kind

=SUMPRODUCT(--ISNUMBER(SEARCH(" Surcharge "," "&H15:H138&" ")),Q15:Q138)

Thanks