Hi,

I am collating project details from multiple sources and need to report back
how many projects are active in specific phases based on data such as in the
table below:
Projects Phase Version "Start Date
W/c" "End Date
W/c"
A Prep
Build
Deliver a1 5-Apr 26-Apr
Close a1 26-Apr 26-Jul
B Prep
Build
Deliver b1 5-Apr 26-Apr
Close b1 26-Apr 26-Jul
C Prep
Build c1 19-Apr 3-May
Deliver c1 5-Apr 19-Apr
Close c2 3-May 2-Aug
D Prep d1 5-Apr 5-Jul
Build d1 26-Jul 2-Aug
Deliver d1 5-Jul 26-Jul
Close d1 2-Aug 1-Nov


If the start date has an entry it means that phase is active. In the table
above I would need to count how many projects are at the 'Deliver' stage for
example.

I have tried a few options and the closed I have got is:
=COUNTIF(Data1!B318,"deliver") however this also counts cells which
contain a null value,

Could anyone help please?

Hi,

Ty this

=sumproduct((Data1!B3:B18="Deliver")*(Data1!D318<>""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" <(E-Mail Removed)> wrote in message
news:48D24CE0-64C2-4978-ACD1-(E-Mail Removed)...
> Hi,
>
> I am collating project details from multiple sources and need to report
> back
> how many projects are active in specific phases based on data such as in
> the
> table below:
> Projects Phase Version "Start Date
> W/c" "End Date
> W/c"
> A Prep
> Build
> Deliver a1 5-Apr 26-Apr
> Close a1 26-Apr 26-Jul
> B Prep
> Build
> Deliver b1 5-Apr 26-Apr
> Close b1 26-Apr 26-Jul
> C Prep
> Build c1 19-Apr 3-May
> Deliver c1 5-Apr 19-Apr
> Close c2 3-May 2-Aug
> D Prep d1 5-Apr 5-Jul
> Build d1 26-Jul 2-Aug
> Deliver d1 5-Jul 26-Jul
> Close d1 2-Aug 1-Nov
>
>
> If the start date has an entry it means that phase is active. In the
> table
> above I would need to count how many projects are at the 'Deliver' stage
> for
> example.
>
> I have tried a few options and the closed I have got is:
> =COUNTIF(Data1!B318,"deliver") however this also counts cells which
> contain a null value,
>
> Could anyone help please?

Thanks for the quick response Ashish however unfortunately this doesn't work
and just returns a value of 0.



"Ashish Mathur" wrote:

> Hi,
>
> Ty this
>
> =sumproduct((Data1!B3:B18="Deliver")*(Data1!D318<>""))
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "CW" <(E-Mail Removed)> wrote in message
> news:48D24CE0-64C2-4978-ACD1-(E-Mail Removed)...
> > Hi,
> >
> > I am collating project details from multiple sources and need to report
> > back
> > how many projects are active in specific phases based on data such as in
> > the
> > table below:
> > Projects Phase Version "Start Date
> > W/c" "End Date
> > W/c"
> > A Prep
> > Build
> > Deliver a1 5-Apr 26-Apr
> > Close a1 26-Apr 26-Jul
> > B Prep
> > Build
> > Deliver b1 5-Apr 26-Apr
> > Close b1 26-Apr 26-Jul
> > C Prep
> > Build c1 19-Apr 3-May
> > Deliver c1 5-Apr 19-Apr
> > Close c2 3-May 2-Aug
> > D Prep d1 5-Apr 5-Jul
> > Build d1 26-Jul 2-Aug
> > Deliver d1 5-Jul 26-Jul
> > Close d1 2-Aug 1-Nov
> >
> >
> > If the start date has an entry it means that phase is active. In the
> > table
> > above I would need to count how many projects are at the 'Deliver' stage
> > for
> > example.
> >
> > I have tried a few options and the closed I have got is:
> > =COUNTIF(Data1!B318,"deliver") however this also counts cells which
> > contain a null value,
> >
> > Could anyone help please?
>

Hi,
formula provided should work, check if you don't have any blank space in the
word Deliver

"CW" wrote:

> Thanks for the quick response Ashish however unfortunately this doesn't work
> and just returns a value of 0.
>
>
>
> "Ashish Mathur" wrote:
>
> > Hi,
> >
> > Ty this
> >
> > =sumproduct((Data1!B3:B18="Deliver")*(Data1!D318<>""))
> >
> > --
> > Regards,
> >
> > Ashish Mathur
> > Microsoft Excel MVP
> > www.ashishmathur.com
> >
> > "CW" <(E-Mail Removed)> wrote in message
> > news:48D24CE0-64C2-4978-ACD1-(E-Mail Removed)...
> > > Hi,
> > >
> > > I am collating project details from multiple sources and need to report
> > > back
> > > how many projects are active in specific phases based on data such as in
> > > the
> > > table below:
> > > Projects Phase Version "Start Date
> > > W/c" "End Date
> > > W/c"
> > > A Prep
> > > Build
> > > Deliver a1 5-Apr 26-Apr
> > > Close a1 26-Apr 26-Jul
> > > B Prep
> > > Build
> > > Deliver b1 5-Apr 26-Apr
> > > Close b1 26-Apr 26-Jul
> > > C Prep
> > > Build c1 19-Apr 3-May
> > > Deliver c1 5-Apr 19-Apr
> > > Close c2 3-May 2-Aug
> > > D Prep d1 5-Apr 5-Jul
> > > Build d1 26-Jul 2-Aug
> > > Deliver d1 5-Jul 26-Jul
> > > Close d1 2-Aug 1-Nov
> > >
> > >
> > > If the start date has an entry it means that phase is active. In the
> > > table
> > > above I would need to count how many projects are at the 'Deliver' stage
> > > for
> > > example.
> > >
> > > I have tried a few options and the closed I have got is:
> > > =COUNTIF(Data1!B318,"deliver") however this also counts cells which
> > > contain a null value,
> > >
> > > Could anyone help please?
> >

Yes, thanks both. This does work

"Eduardo" wrote:

> Hi,
> formula provided should work, check if you don't have any blank space in the
> word Deliver
>
> "CW" wrote:
>
> > Thanks for the quick response Ashish however unfortunately this doesn't work
> > and just returns a value of 0.
> >
> >
> >
> > "Ashish Mathur" wrote:
> >
> > > Hi,
> > >
> > > Ty this
> > >
> > > =sumproduct((Data1!B3:B18="Deliver")*(Data1!D318<>""))
> > >
> > > --
> > > Regards,
> > >
> > > Ashish Mathur
> > > Microsoft Excel MVP
> > > www.ashishmathur.com
> > >
> > > "CW" <(E-Mail Removed)> wrote in message
> > > news:48D24CE0-64C2-4978-ACD1-(E-Mail Removed)...
> > > > Hi,
> > > >
> > > > I am collating project details from multiple sources and need to report
> > > > back
> > > > how many projects are active in specific phases based on data such as in
> > > > the
> > > > table below:
> > > > Projects Phase Version "Start Date
> > > > W/c" "End Date
> > > > W/c"
> > > > A Prep
> > > > Build
> > > > Deliver a1 5-Apr 26-Apr
> > > > Close a1 26-Apr 26-Jul
> > > > B Prep
> > > > Build
> > > > Deliver b1 5-Apr 26-Apr
> > > > Close b1 26-Apr 26-Jul
> > > > C Prep
> > > > Build c1 19-Apr 3-May
> > > > Deliver c1 5-Apr 19-Apr
> > > > Close c2 3-May 2-Aug
> > > > D Prep d1 5-Apr 5-Jul
> > > > Build d1 26-Jul 2-Aug
> > > > Deliver d1 5-Jul 26-Jul
> > > > Close d1 2-Aug 1-Nov
> > > >
> > > >
> > > > If the start date has an entry it means that phase is active. In the
> > > > table
> > > > above I would need to count how many projects are at the 'Deliver' stage
> > > > for
> > > > example.
> > > >
> > > > I have tried a few options and the closed I have got is:
> > > > =COUNTIF(Data1!B318,"deliver") however this also counts cells which
> > > > contain a null value,
> > > >
> > > > Could anyone help please?
> > >

Hi,

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" <(E-Mail Removed)> wrote in message
news:776B5984-3815-4F9F-B606-(E-Mail Removed)...
> Yes, thanks both. This does work
>
> "Eduardo" wrote:
>
>> Hi,
>> formula provided should work, check if you don't have any blank space in
>> the
>> word Deliver
>>
>> "CW" wrote:
>>
>> > Thanks for the quick response Ashish however unfortunately this doesn't
>> > work
>> > and just returns a value of 0.
>> >
>> >
>> >
>> > "Ashish Mathur" wrote:
>> >
>> > > Hi,
>> > >
>> > > Ty this
>> > >
>> > > =sumproduct((Data1!B3:B18="Deliver")*(Data1!D318<>""))
>> > >
>> > > --
>> > > Regards,
>> > >
>> > > Ashish Mathur
>> > > Microsoft Excel MVP
>> > > www.ashishmathur.com
>> > >
>> > > "CW" <(E-Mail Removed)> wrote in message
>> > > news:48D24CE0-64C2-4978-ACD1-(E-Mail Removed)...
>> > > > Hi,
>> > > >
>> > > > I am collating project details from multiple sources and need to
>> > > > report
>> > > > back
>> > > > how many projects are active in specific phases based on data such
>> > > > as in
>> > > > the
>> > > > table below:
>> > > > Projects Phase Version "Start Date
>> > > > W/c" "End Date
>> > > > W/c"
>> > > > A Prep
>> > > > Build
>> > > > Deliver a1 5-Apr 26-Apr
>> > > > Close a1 26-Apr 26-Jul
>> > > > B Prep
>> > > > Build
>> > > > Deliver b1 5-Apr 26-Apr
>> > > > Close b1 26-Apr 26-Jul
>> > > > C Prep
>> > > > Build c1 19-Apr 3-May
>> > > > Deliver c1 5-Apr 19-Apr
>> > > > Close c2 3-May 2-Aug
>> > > > D Prep d1 5-Apr 5-Jul
>> > > > Build d1 26-Jul 2-Aug
>> > > > Deliver d1 5-Jul 26-Jul
>> > > > Close d1 2-Aug 1-Nov
>> > > >
>> > > >
>> > > > If the start date has an entry it means that phase is active. In
>> > > > the
>> > > > table
>> > > > above I would need to count how many projects are at the 'Deliver'
>> > > > stage
>> > > > for
>> > > > example.
>> > > >
>> > > > I have tried a few options and the closed I have got is:
>> > > > =COUNTIF(Data1!B318,"deliver") however this also counts cells
>> > > > which
>> > > > contain a null value,
>> > > >
>> > > > Could anyone help please?
>> > >