| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to return an array of values that are not zero/Null | Excel Discussion (Misc queries) | |||
| How to return an array of values that are not zero/Null | Excel Discussion (Misc queries) | |||
| Replace Null Values in a Column | Excel Discussion (Misc queries) | |||
| Array: Counting multiple values within array | Excel Worksheet Functions | |||
| Array Pasted from One Worksheet Converts Null Values to Zeros | Excel Discussion (Misc queries) | |||
Hybrid Mode
