Sumproduct formula help

[U_Shrestha]

Can someone please help me with this formula?

SUMPRODUCT((MONTH(Issues_Closed_On)=1)*YEAR((Issues_Closed_On)=2008)*(Issue _Status="Active")*(Job_Assigned_To="Charlie"))

I have 3-columns and Named ranges, namely, Issues_Closed_On, Issue_Status and Job_Assigned_To. Issues_Closed_On has list of dates.

I need to find the no. of "Active" in the month of January where the Job_Assigned_To was "Charlile". I am getting value error in my formula.

Thanks.

[mdmackillop]

Can you post a sample to save us having to recreate it?

[U_Shrestha]

Sure, I have attached a sample sheet. Actually, my actual purpose of this table is to create a dynamic chart to check the no. of Active issues for each for 4-responsible parties in a given time. I don't know how to create a dynamic chart that shows a no. of Active issues (in my table) in a given time.

So I thought if a can calculate a summary of Active issue for each month I can make a chart that shows the no. of Active issues for each month.

As for this problem, another hurdle is, under the heading "Trinity", I need the sumproduct value based on Issue_Closed_On=Month(1),Issue_Status="Active",Job_Assigned_To = Trinity*(anything with "Trinity)

[Bob Phillips]

=SUMPRODUCT(--(MONTH(Issues_Closed_On)=1),--(YEAR(Issues_Closed_On)=2008),--(Issue_Status="Active"),--(Job_Assigned_To="Charlie"))

[U_Shrestha]

Hello xld,

I am still getting the value error in L5 of the attached sheet. Thanks.

[Bob Phillips]

You need to change the range names to start at row 3, and corect the invalid value in G16.

[U_Shrestha]

Thanks xld. I just found where I made the blunder.

I needed to find How many Active Issues were there in the Month of January, Feb and so forth, but if the data in column H is active then value in Column G(Issue_Status) will always be empty.

Now, is it possible to count how many blanks were there in each month or Is it possible to count how many "Active" were there in Column "Issue_Statues"? Both condition would give the same answer because, if "Issue_Status" is "Closed" then its corresponding cell in "Issues_Closed_On" would have a date.

[Bob Phillips]

Now, is it possible to count how many blanks were there in each month

=SUMPRODUCT(--(MONTH(Issues_Closed_On)=1),--(YEAR(Issues_Closed_On)=2008),--(Issue_Status=""))

[U_Shrestha]

I think this problem requires a different approach since I am getting zero by using the formula. I think answer lies between columns Issues_Closed_On and Job_Assigned_To only.
=SUMPRODUCT(--(MONTH(Issues_Closed_On)=1),--(YEAR(Issues_Closed_On)=2008),--(Issue_Status=""))

To summarize the situation, "Issues_Closed_On" can either be Blank (meaning issue is still active) or a Date (issue closure date). I need to find how many blanks were there in a given month (jan, feb, mar, so forth). If a cell (Eg. G99), is blank until today then this would be one of the Issue that was Active at the end of January, February and March, there may be others too, but if it was closed on 3/12/08 the it would be counted as an Active issue for Jan and Feb.

Looking at the attached table, what is the best way to calculate total no. of Active issues in each month for "Charlie"? Would it be simpler if we calculate Total No. of Issues in a given month and then divide it into Active and Closed issues?

[Bob Phillips]

Is this closer

=SUMPRODUCT(--((Issues_Closed_On="")+(Issues_Closed_On>=TODAY())),--(Issue_Status=""))

[U_Shrestha]

No, I am afraid. With this formula
[=sumproduct((Issues_Closed_On="")*(Job_Assigned_To="Charlie"))] I got 34, which is the total no. of Active Issues For March. But since various issues were closed on Jan and Feb, its Active no. of issues should be different.

[Bob Phillips]

That is not what I suggested.

[U_Shrestha]

I tried your formula but I didn't get the correct number. Thanks.

[Bob Phillips]

Okay!