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.

 

[Oeldere]

maybe this way.

Excel Workbook
	A	B	C	D	E	F	G	H
1	data => text to collumn
2	Task	Duration	Hrs	Start	Date	year	month	day
3	A	3	hrs	1-2-2012	8:00	2012	2	1
4	B	6	hrs	6-2-2012	16:00	2012	2	6
5
6
7		9
Blad6

Cell Formulas
Range	Formula
F3	=YEAR(D3)
F4	=YEAR(D4)
G3	=MONTH(D3)
G4	=MONTH(D4)
H3	=DAY(D3)
H4	=DAY(D4)
B7	=SUMPRODUCT((G3:G4=2)*(B3:B4))

 

[T. Valko]

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.

It looks like your time range is for the entire month of Feb 2012. Are all the dates within the same year?

 

[jc77]

Oeldere:
Unfortunately, my data is a copy/paste from MS Project so I really can't reorganize the columns and the data. I'm trying to automate this process so that I can analyze a schedule that may have several hundred tasks.

I already am using sumproduct to calculate the number of tasks that fit w/in a certain month. But now, I actually need to sum the duration hrs associated w/ those tasks.

Thank you for your help.


T. Valko:
My example only included Feb for simpler explanation. In actuality, the tasks can be any time in the 2012 and 2013. My true goal is to total the task duration hrs in each month. I'm using the start date as my condition.

Thank you for your help.

 

[Oeldere]

I already am using sumproduct to calculate the number of tasks that fit w/in a certain month. But now, I actually need to sum the duration hrs associated w/ those tasks.

Please, show us your formula, maybe it gives new ideas.

 

[T. Valko]

Oeldere:
Unfortunately, my data is a copy/paste from MS Project so I really can't reorganize the columns and the data. I'm trying to automate this process so that I can analyze a schedule that may have several hundred tasks.

I already am using sumproduct to calculate the number of tasks that fit w/in a certain month. But now, I actually need to sum the duration hrs associated w/ those tasks.

Thank you for your help.


T. Valko:
My example only included Feb for simpler explanation. In actuality, the tasks can be any time in the 2012 and 2013. My true goal is to total the task duration hrs in each month. I'm using the start date as my condition.

Thank you for your help.

What version of Excel are you using?

 

[jc77]

Here's what I'm using right now.

=SUMIFS(B37:B38,C37:C38,"<=2/29/12 23:59",C37:C38,">=2/1/12 0:00")

B37:B38 is the column of task duration hours
C37:C38 is the column of tasks start dates

This works if Column B is nothing but numerical values. In my case, however, I have a number followed by "_hrs"

I tried to embed: (LEFT(B37:38,LEN(B37:38)-4))

in the place of B37:38 in the above sumif formula but Excel didn't like that.

 

[jc77]

I'm using Excel 2007

 

[T. Valko]

I'm using Excel 2007

Try this...

Book1
	A	B	C	D	E	F
1	Task	Duration Hrs	Start Date	_	Month	Total
2	A	3 hrs	2/1/2012 8:00	_	2/1/2012	18.5
3	B	6 hrs	2/6/2012 16:00	_	_	_
4	C	10 hrs	1/1/2012 14:15	_	_	_
5	D	2.5 hrs	2/28/2012 10:25	_	_	_
6	E	2 hrs	1/31/2012 23:35	_	_	_
7	F	2 hrs	3/1/2012 0:15	_	_	_
8	G	7 hrs	2/29/2012 23:59	_	_	_
9	H	1 hr	2/10/2013 1:20	_	_	_
Sheet1

This array formula** entered in F2:

=SUM(IF(TEXT(C2:C9,"mmmyy")=TEXT(E2,"mmmyy"),--LEFT(B2:B9,FIND(" ",B2:B9))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Oeldere]

Unfortunately, my data is a copy/paste from MS Project so I really can't reorganize the columns and the data

If you want to calculate a lot in this workbook I advise you to reconsider the format of the workbook.