Count values in array of data where no blanks in one column
 

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!B3:D18,"deliver") however this also counts cells which
contain a null value,

Could anyone help please?

Count values in array of data where no blanks in one column
 

Try
=SUMPRODUCT((B3:B100="Delivered")*(ISNUMBER(D3:D10 0)))

--
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!B3:D18,"deliver") however this also counts cells which
contain a null value,

Could anyone help please?

Count values in array of data where no blanks in one column
 

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(D3:D10 0)))

--
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!B3:D18,"deliver") however this also counts cells which
contain a null value,

Could anyone help please?

Count values in array of data where no blanks in one column
 

--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(D3:D100) ))


--
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(D3:D10 0)))

--
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!B3:D18,"deliver") however this also counts cells which
contain a null value,

Could anyone help please?

Count values in array of data where no blanks in one column
 

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!D112:D146<""))

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(D3:D100) ))


--
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(D3:D10 0)))

--
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!B3:D18,"deliver") however this also counts cells which
contain a null value,

Could anyone help please?