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?

Try
=SUMPRODUCT((B3:B100="Delivered")*(ISNUMBER(D3100)))

--
Jacob (MVP - Excel)


"CW" wrote:

> 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?

Apologies all, I seems to have posted this question twice by accident.

Hi Jacob,
Thanks but unfortunately this just returns 0 values. Any other ideas?

Thanks


"Jacob Skaria" wrote:

> Try
> =SUMPRODUCT((B3:B100="Delivered")*(ISNUMBER(D3100)))
>
> --
> Jacob (MVP - Excel)
>
>
> "CW" wrote:
>
> > 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?

--Check whether the text you are looking for is exactly 'Deliver'.. without
any spaces in front or after...
--Also I assume the dates are in exxcel date format....

In the formula I provided earlier it was 'Delivered'
=SUMPRODUCT((B3:B100="Deliver")*(ISNUMBER(D3100)))


--
Jacob (MVP - Excel)


"CW" wrote:

> Apologies all, I seems to have posted this question twice by accident.
>
> Hi Jacob,
> Thanks but unfortunately this just returns 0 values. Any other ideas?
>
> Thanks
>
>
> "Jacob Skaria" wrote:
>
> > Try
> > =SUMPRODUCT((B3:B100="Delivered")*(ISNUMBER(D3100)))
> >
> > --
> > Jacob (MVP - Excel)
> >
> >
> > "CW" wrote:
> >
> > > 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 Jacob, I think we have it. It actually works without the ISNUMBER
part. When I tested it first I was using the wildcard as the actual values I
am using are longer than the sample data provided.
When I put in the full string it works, e.g.
=SUMPRODUCT((Data1!B112:B146="Modelling / R&D")*(Data1!D112146<>""))

Thanks for your help


"Jacob Skaria" wrote:

> --Check whether the text you are looking for is exactly 'Deliver'.. without
> any spaces in front or after...
> --Also I assume the dates are in exxcel date format....
>
> In the formula I provided earlier it was 'Delivered'
> =SUMPRODUCT((B3:B100="Deliver")*(ISNUMBER(D3100)))
>
>
> --
> Jacob (MVP - Excel)
>
>
> "CW" wrote:
>
> > Apologies all, I seems to have posted this question twice by accident.
> >
> > Hi Jacob,
> > Thanks but unfortunately this just returns 0 values. Any other ideas?
> >
> > Thanks
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Try
> > > =SUMPRODUCT((B3:B100="Delivered")*(ISNUMBER(D3100)))
> > >
> > > --
> > > Jacob (MVP - Excel)
> > >
> > >
> > > "CW" wrote:
> > >
> > > > 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?