Formula to add values

[Howardc]

Thanks for the reply

1) The formula will need to add all values from 3000 to 3199
2) I need a seperate formula to add the values containing a D at the end for eg 3000D, 3001D, 3110D etc

[Rick Rothstein]

Thanks for the reply

1) The formula will need to add all values from 3000 to 3199
2) I need a seperate formula to add the values containing a D at the end for eg 3000D, 3001D, 3110D etc

Give these formulas a try...

Numbers without "D": =SUMPRODUCT(F1:F1000*(E1:E1000>2999)*(E1:E1000<320 0))

Numbers with "D": =SUMPRODUCT(F1:F43*(1*REPLACE(E1:E43,LEN(E1:E43),1 ,"")>2999)*(1*REPLACE(E1:E43,LEN(E1:E43),1,"")<320 0))

Note: The first formula will work if the specified range is larger than actual range of data; however, the second formula won't... its range must be sized to the actual range of data (blanks will make the formula return an error).