If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!B318,"deliver") however this also counts cells which contain a null value, Could anyone help please? |
#2
|
|||
|
|||
Counting values in an array where value in one column is not null
Hi,
Ty this =sumproduct((Data1!B3:B18="Deliver")*(Data1!D318 "")) -- 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!B318,"deliver") however this also counts cells which contain a null value, Could anyone help please? |
#3
|
|||
|
|||
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!D318 "")) -- 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!B318,"deliver") however this also counts cells which contain a null value, Could anyone help please? |
#4
|
|||
|
|||
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!D318 "")) -- 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!B318,"deliver") however this also counts cells which contain a null value, Could anyone help please? |
#5
|
|||
|
|||
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!D318 "")) -- 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!B318,"deliver") however this also counts cells which contain a null value, Could anyone help please? |
#6
|
|||
|
|||
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!D318 "")) -- 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!B318,"deliver") however this also counts cells which contain a null value, Could anyone help please? |
Thread Tools | |
Display Modes | |
|
|