Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count Blank Cells with Conditions
I am trying to count blanks cells with conditions.
In column 'A' I have a list of dates and in column 'B' I have a list of group numbers (two digits, formatted as text - to preserve two digits under the value of 10). Some of the cells in column 'B' are blank (no group number assigned). I need to count the blank cells that occur between a specific date range. I have tried countif, sumproduct, and nested if functions but I am just not getting it right. I am either getting a value of 0, false or just a plain error. Any help would be greatly appreciated. Last edited by CNWatsonJr : March 1st 12 at 12:26 AM Reason: add image |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blank Cells with Conditions
On 1/03/2012 11:11 AM, CNWatsonJr wrote:
I am trying to count blanks cells with conditions. In column 'A' I have a list of dates and in column 'B' I have a list of group numbers (two digits, formatted as text - to preserve two digits under the value of 10). Some of the cells in column 'B' are blank (no group number assigned). I need to count the blank cells that occur between a specific date range. I have tried countif, sumproduct, and nested if functions but I am just not getting it right. I am either getting a value of 0, false or just a plain error. Any help would be greatly appreciated. +-------------------------------------------------------------------+ |Filename: excelshot.jpg | |Download: http://www.excelbanter.com/attachment.php?attachmentid=301| +-------------------------------------------------------------------+ Hi As for using Text for preservation of your numbering format, you can use a Custom Number Format that = 00 which will display any single digit number with a leading zero. This will get you counts of the specific criteria of each Group, although I could not get the right syntax for the additional date range criteria to work. Group. 1 =COUNTIF($B$2:$B$10,"=01") Group. 2 =COUNTIF($B$2:$B$10,"=02") Group. 3 =COUNTIF($B$2:$B$10,"=03") Group. 4 =COUNTIF($B$2:$B$10,"=04") Non-Grouped =COUNTIF($B$2:$B$10,"") HTH Mick. |
#3
|
|||
|
|||
Thank you for your reply. I have been able to count the non-grouped entries without using the date condition, even keeping the group field as text. This is a very small portion of the data set I am given, but it is the problem I haven't been able to solve yet (adding the date condition so I can count only those that are entered in a specified time frame instead of all of them in the database).
I am using the index, rank and match functions to sort the groups and rank them but I haven't been able to include the entries that are not assigned a group number because I can't count the ones in the timeframe I am looking at. The formula I am using to count the grouped entries is: countifs(Data!$I:$I, "="&L25, Data!$D:$D, "="&Dates!$C:$3) This formula is in K25 if it matters. The length of the database varies from week to week but I can find the last row and create a reference and then use the INDIRECT function if I have to. I didn't include all of this before because I thought it would just confuse the issue. This is really perplexing me. Quote:
|
#4
|
|||
|
|||
Okay - so, I determined that my formula is correct and that COUNTIFS will work with empty cells and conditions (like between two dates). It seems my problem is that there is a unknown value written to the cell and it just looks blank or empty.
Now, If I can only figure out what is in those cells. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting non-blank cells with conditions | Excel Discussion (Misc queries) | |||
count nonblank cells with conditions | Excel Worksheet Functions | |||
Count and Sum of cells if conditions fulfilled | Excel Worksheet Functions | |||
I want to count relying upon conditions in other cells | Excel Discussion (Misc queries) | |||
Count the number of Cells in one ROW with conditions | Excel Worksheet Functions |