| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Blanks bw Data | Excel Discussion (Misc queries) | |||
| Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
| Counting multiple values (including blanks) in one column | Excel Discussion (Misc queries) | |||
| Count Non-Blanks in one column based on criteria in another | Excel Worksheet Functions | |||
| Count Blanks in a Filtered Column | Excel Worksheet Functions | |||
Linear Mode
