SumIf not working here..

[mhoot20]

I have Projects, Start Date, End Date, and Hrs columns in one sheet that shows how man hours each group needs to spend on a project that has a given date range for duration.
E.g
Hours
Project Start Date End Date BD Finance etc...
1 8/31/2013 10/12/2013 8 3
2 10/7/2013 11/21/2013 8 4
3 9/7/2013 10/17/2013 8 5

to this..

Sep-13 Oct-13 Nov-13

BD 16 24 0.2
IT 0.5 0.2 0.2
Finance 8 12 0.2

I'm looking to compile the data into another table that sums all hrs by month for all projects, and by function. So any project listed that has an open period during the month of Sept, it sums the hrs columns for that function. So it looks like a SumIf function with a criteria of a date range (9/1/2013-/31/2013) that falls within a criteria range of the start dates and end dates. It seems to be this criteria that is the problem, if I just use one date it works out ok, but I need it to sum only if project falls within the given month dates. I haven't been able to figure out how to combine this into one formula that works.

Let me know if you think a sumif would work of if something else might be better. Thanks.

Matt

[Pete_UK]

You would use SUMIFS instead of SUMIF, as this allows you to use two or more criteria, i.e. greater than or equal to the start date AND less than or equal to the end date. Note that the syntax is a bit different than for SUMIF, as the sum_range is the first parameter, followed by pairs of condition_range and condition.

Hope this helps.

Pete

[FDibbins]

Hi and welcome to the forum

Take a look at using the sumifS() function, it allows fpor more criteria than the sumif() function

[azumi]

Could you try to upload your problem workbook and your expectation result, so we can figure it out the solution

Azumi

[kvsrinivasamurthy]

For NOV-13 it is shown as 0.2.How it is calculated.

[mhoot20]

Thanks for the quick responses everybody. I should have posted the formulas I have been trying out because I actually have been trying the sumifs, not sumif. Also, sorry but I cant post the file for confidentiality reasons. You can also forget the 0.2 for right now, was just a sample, I'm going to convert the hrs to FTE, but first I'm just trying to get the hours right. Below are two sumifs formulas I've been trying, but cant get to work properly.

In the formula, the AA columns are the hours column for a particular function, say BD. The N column is the Start Date of a project, and the O column is the End Date of the project. The 9/1/2013 and 10/1/2013 is because in this formula I'm trying to the sum the hours of BD for all the projects that are open in September.

=SUMIFS('CI Project Tracker'!$AA$6:$AA$1048576,'CI Project Tracker'!$N$6:$N$1048576,OR("<=9/1/2013",">=9/1/2013","<10/1/2013"),'CI Project Tracker'!$O$6:$O$1048576,">10/1/2013")

=SUMIFS('CI Project Tracker'!AA6:AA1048576,'CI Project Tracker'!N6:N1048576,OR("<9/1/2013",(AND(">9/1/2013","<10/1/2013"))),'CI Project Tracker'!O6:O1048576,">10/1/2013")

Both of these formulas are giving me 0 when they should giving me the sum of hours that BD has for every project that is open in Sept (which is non-0)

If I can provide any other info let me know. Thanks.

[mhoot20]

Actually, attached is a simplified version of what I'm trying to do. The second table is the sum of hours I'd like to
see for the given project chart (its rough cut so if the project falls in the month we'll count it)

Matt

[kvsrinivasamurthy]

Pl see the attached file.

[mhoot20]

Wow, that's really great kvsrinivasamurthy! Looks like exactly what I'm trying to do, hadn't thought to do it that way.

I'm plugging in the formula now to my sheets and so far so good. i'm just going to try some diff project conditions out before I mark as solved.

Thanks a lot! I really appreciate it.

[mhoot20]

Ok, have run into some problems after testing. It seems to be in the match function portion of the formula. I need an
exact match for the name, whereas its returning something else and than ends up summing the wrong column or not being able to find the match (e.g. aftermarket).

I've tried to add the exact portion to the match portion but to no avail yet. Any ideas on how to fix this portion of the formula?

Matt

[mhoot20]

Nvm, was my mistake. Seems adding the match type 0 to match function is now working, may have been some formatting or case sensitivity on the cells.

Thanks for all your help! I'll mark as solved now.

Matt

[kvsrinivasamurthy]

Thanks for the feedback