Hello,

I have a sheet that has a status (active, closed, on-hold) and a couple of
date fields (target complete date, date completed).

I would like to compute some simple metrics such as "active" deals that are
"due next week."

In the previous example, I need to be able to count the total number of
"active" deals where the "target complete date" is in the next seven days.

I believe I can use formulas (countif, between, etc) to compute these
metrics? Any other suggestions? Pivot tables?

Open to any and all suggestions.

Thanks for you feedback.

Bryan

I would go with pivot tables or use the sumproduct formula depending on
exactly how you want the data to look...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Bryan" wrote:

Hello,

I have a sheet that has a status (active, closed, on-hold) and a couple of
date fields (target complete date, date completed).

I would like to compute some simple metrics such as "active" deals that are
"due next week."

In the previous example, I need to be able to count the total number of
"active" deals where the "target complete date" is in the next seven days.

I believe I can use formulas (countif, between, etc) to compute these
metrics? Any other suggestions? Pivot tables?

Open to any and all suggestions.

Thanks for you feedback.

Bryan

Hi Jim,

Thanks for the reference to the website. There is some great content there.

Since I'm not great with pivot tables I'm trying to work with sumproduct.
I've got the first part down but am having some difficulty with the dates. I
was hoping you or someone else could help.

Here is my data set:

Status Due Date
Active 4/6/2006
Active 4/7/2006
Active 4/12/2006
Active 4/28/2006

The following equation equals "4" as expected.

=SUMPRODUCT((A2:A8="Active")*1)

How do I build the latter half of the SUMPRODUCT function to return only
active deals that are due next week (i.e., where the due date is today plus
5)?

Thanks again for your help.

Bryan


"Jim Thomlinson" wrote:

I would go with pivot tables or use the sumproduct formula depending on
exactly how you want the data to look...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Bryan" wrote:

Hello,

I have a sheet that has a status (active, closed, on-hold) and a couple of
date fields (target complete date, date completed).

I would like to compute some simple metrics such as "active" deals that are
"due next week."

In the previous example, I need to be able to count the total number of
"active" deals where the "target complete date" is in the next seven days.

I believe I can use formulas (countif, between, etc) to compute these
metrics? Any other suggestions? Pivot tables?

Open to any and all suggestions.

Thanks for you feedback.

Bryan

=SUMPRODUCT(($A$2:$A$5="Active")*($B$2:$B$5=TODAY ())*($B$2:$B$5<=TODAY()+5))
--
HTH...

Jim Thomlinson


"Bryan" wrote:

Hi Jim,

Thanks for the reference to the website. There is some great content there.

Since I'm not great with pivot tables I'm trying to work with sumproduct.
I've got the first part down but am having some difficulty with the dates. I
was hoping you or someone else could help.

Here is my data set:

Status Due Date
Active 4/6/2006
Active 4/7/2006
Active 4/12/2006
Active 4/28/2006

The following equation equals "4" as expected.

=SUMPRODUCT((A2:A8="Active")*1)

How do I build the latter half of the SUMPRODUCT function to return only
active deals that are due next week (i.e., where the due date is today plus
5)?

Thanks again for your help.

Bryan


"Jim Thomlinson" wrote:

I would go with pivot tables or use the sumproduct formula depending on
exactly how you want the data to look...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Bryan" wrote:

Hello,

I have a sheet that has a status (active, closed, on-hold) and a couple of
date fields (target complete date, date completed).

I would like to compute some simple metrics such as "active" deals that are
"due next week."

In the previous example, I need to be able to count the total number of
"active" deals where the "target complete date" is in the next seven days.

I believe I can use formulas (countif, between, etc) to compute these
metrics? Any other suggestions? Pivot tables?

Open to any and all suggestions.

Thanks for you feedback.

Bryan