Counting values in an array where value in one column is not null
 

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?

Counting values in an array where value in one column is not null
 

Hi,

Ty this

=sumproduct((Data1!B3:B18="Deliver")*(Data1!D3:D18 <""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
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?

Counting values in an array where value in one column is not n
 

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!D3:D18 <""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
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?

Counting values in an array where value in one column is not n
 

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!D3:D18 <""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
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?

Counting values in an array where value in one column is not n
 

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!D3:D18 <""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
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?

Counting values in an array where value in one column is not n
 

Hi,

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
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!D3:D18 <""))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
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?