Trying to sum data that fits certain criteria

[jc77]

I can't seem to figure this out.

I'm trying to sum data that fits w/in two dates I've specified. Normally, this would be a simple sumif formula. But the data I'm actually summing has text string and so Excel doesn't see the numerical values to be able to add them.

Here's what my data table looks like.

Task / Duration Hrs / Start Date
A / 3 hrs / 2/1/12 08:00
B / 6 hrs / 2/6/12 16:00

I want to sum the total duration hrs for all tasks that start sometime between "Feb 1 0:00" and "Feb 29 23:59" so I want Excel to return the value "9" in this example. I know how to use LEFT LEN to return duration hours values if I was evaluating each cell individually but not sure how to combine if w/ the sumif formula if I want to analyze an entire worksheet of data.

Should I be using another formula? Thank you very much for your help.

 

[jc77]

Thank you very much, T. Valko. That works great!

Is this:
--LEFT(B2:B9,FIND(" ",B2:B9)

the portion of the formula that returns the numerical value?

What does FIND " " mean?

 

[T. Valko]

Thank you very much, T. Valko. That works great!

You're welcome!

Is this:
--LEFT(B2:B9,FIND(" ",B2:B9)

the portion of the formula that returns the numerical value?

What does FIND " " mean?

FIND(" ", means to find the position number of the space character in the Duration Hrs range. We want to extract the numbers to the left of the space character.

So, this part of the formula:

--LEFT(B2:B9,FIND(" ",B2:B9)

Evaluates this:

Book1
	B
1	Duration Hrs
2	3 hrs
3	6 hrs
4	10 hrs
5	2.5 hrs
6	2 hrs
7	2 hrs
8	7 hrs
9	1 hr
Sheet1

Like this:

Book1
	B
1	Duration Hrs
2	3
3	6
4	10
5	2.5
6	2
7	2
8	7
9	1
Sheet1

So that these numbers can then be summed when they fall within the date range.

 

[jc77]

Wow. Good to know. I will remember that for the future.

Thank you very much, once again. You are awesome!!!

 

[T. Valko]

Wow. Good to know. I will remember that for the future.

Thank you very much, once again. You are awesome!!!

You're welcome. Thanks for the feedback!