Microsoft Excel - Summing values from a rage containing numerical and textual substring

Asked By John on 11-Mar-10 02:25 AM

Summing values from a rage containing numerical and textual substring

I have an excel file there quantity I added with date like 09/05/2009=05 and 10/05/2009=05 as on.  Each date and quantity added on each column. I want to sum the quantity.

Example         5/12=2,   6/12=30, 7/12=5, 8/12=30, 9/12=30, 16/12=30

More clearly

I would want to be able to sum a range of cells that contain text like 5/12=2,   6/12=30, 7/12=5, 8/12=30, 9/12=30, and 16/12=30. And in the instant case the value you would want to arrive at with these text strings would be 127 = (2 + 30 + 5 + 30 + 30 + 30).

Is there any way to sum this quantity without date?

I got this formula

=SUM(IF(ISNUMBER(FIND(C2,B2:B5)),VALUE(LEFT(B2:B5,FIND(C2,B2:B5)-1)),0))

but its not working  as it is.

I would suggest a UDF for this. - Scott Gall replied to John on 09-Jun-10 07:14 PM

Given the many cases involved in quarterizing dates and applying math to them I would suggest writing a UDF for this.

If you still require one let me know and I'd be happy to take a closer look at it.

John replied to Scott Gall on 18-Aug-10 01:50 AM
Thank you for replay me.

yes I want a UDF for this.