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]

Many many thanks Aladin,

You are welcome.

Just one more tho if you would be so kind

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

...

What is the issue that you have with this formula?

 

[ExcelRoy]

hi aladin,

sorry i have not explained myself very well. apologies

this formula shows a zero until criteria is entered within the parameters

i would like it to show "" until criteria is entered

thanks

 

[Aladin Akyurek]

hi aladin,

sorry i have not explained myself very well. apologies

this formula shows a zero until criteria is entered within the parameters

i would like it to show "" until criteria is entered

thanks

Do you mean something like...

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

which checks whether the string Surcharge occurs at all in H15:H138?

By the way, you can also keep the original formula as is and
custom format the formula cell as:

[=0]"";General