Count Blank Cells with Conditions

[CNWatsonJr]

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 o
group numbers (two digits, formatted as text - to preserve two digit
under the value of 10). Some of the cells in column 'B' are blank (n
group number assigned).

I need to count the blank cells that occur between a specific dat
range.

I have tried countif, sumproduct, and nested if functions but I am jus
not getting it right. I am either getting a value of 0, false or just
plain error.

Any help would be greatly appreciated

+-------------------------------------------------------------------
|Filename: excelshot.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=301
+-------------------------------------------------------------------

 

[Vacuum Sealed]

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.

 

[CNWatsonJr]

Thank you for your reply. I have been able to count the non-groupe
entries without using the date condition, even keeping the group fiel
as text. This is a very small portion of the data set I am given, but i
is the problem I haven't been able to solve yet (adding the dat
condition so I can count only those that are entered in a specified tim
frame instead of all of them in the database).

I am using the index, rank and match functions to sort the groups an
rank them but I haven't been able to include the entries that are no
assigned a group number because I can't count the ones in the timefram
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 th
last row and create a reference and then use the INDIRECT function if
have to.

I didn't include all of this before because I thought it would jus
confuse the issue.

This is really perplexing me.

On 1/03/2012 11:11 AM, CNWatsonJr wrote:-
Hi

As for using Text for preservation of your numbering format, you can us

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 rang

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[CNWatsonJr]

Okay - so, I determined that my formula is correct and that COUNTIF
will work with empty cells and conditions (like between two dates). I
seems my problem is that there is a unknown value written to the cel
and it just looks blank or empty.

Now, If I can only figure out what is in those cells

+-------------------------------------------------------------------
+-------------------------------------------------------------------