Count Blank Cells Within Date Range

[Coucher]

Trying to count the number of blank cells within a date range.

For example...

B3:B2500 could either have data or be blank.

C3:C2500 has a calendar date, for example 04/01/2011.

D3:D14 has the first day of each month, for example, 01/01/2011, 02/01/2011, 03/01/2011, etc

F3:F14 has the last day of each month, for example, 01/31/2011, 02/28/2011, 03/31/2011, etc

So, I want to count the cells in B3:B2500, if blank, and if between D3 to F3, or D4 to F4, so on & so on.

I've toyed around with COUNTBLANK, COUNTIF, & SUMPRODUCT formatting each different ways but can't seem to get to work.

Any insight or assistance is appreciated in advance.

Thanks!

 

[T. Valko]

Trying to count the number of blank cells within a date range.

For example...

B3:B2500 could either have data or be blank.

C3:C2500 has a calendar date, for example 04/01/2011.

D3:D14 has the first day of each month, for example, 01/01/2011, 02/01/2011, 03/01/2011, etc

F3:F14 has the last day of each month, for example, 01/31/2011, 02/28/2011, 03/31/2011, etc

So, I want to count the cells in B3:B2500, if blank, and if between D3 to F3, or D4 to F4, so on & so on.

I've toyed around with COUNTBLANK, COUNTIF, & SUMPRODUCT formatting each different ways but can't seem to get to work.

Any insight or assistance is appreciated in advance.

Thanks!

One way...

Entered in F3 and copied down to F14:

=SUMPRODUCT(--(B$3:B$2500=""),--(C$3:C$2500>=D3),--(C$3:C$2500<=E3))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF($C$3:$C$2500>=K3,IF($C$3:$C$2500<=L3,1-($B$3:$B$2500=""))))

This counts non-blank and non-empty data cells.

 

[Jared_8878]

=rows(i1:i8)-sumproduct(--(i1:i8<>""))

 

[Coucher]

One way...

Entered in F3 and copied down to F14:

=SUMPRODUCT(--(B$3:B$2500=""),--(C$3:C$2500>=D3),--(C$3:C$2500<=E3))

Substituted my correct cell locations for the generic ones used in the example I provided and this one worked great.

Thanks!

ray:

 

[Coucher]

**Edit**

I'm an idiot, saw a syntax error I had referencing the wrong range for the dates. Works great.

 

[T. Valko]

Substituted my correct cell locations for the generic ones used in the example I provided and this one worked great.

Thanks!

ray:

You're welcome. Thanks for the feedback!

 

[Motheo]

Substituted my correct cell locations for the generic ones used in the example I provided and this one worked great.

Thanks!

?

I want to use this function but my data is in a different sheet and I need to do calculations on a new sheet. Please assist

 

[Peter_SSs]

I want to use this function but my data is in a different sheet and I need to do calculations on a new sheet. Please assist

An easy way is to set up the formula and get it working on the same sheet as the data as suggested above and then Cut that formula cell and Paste it into the other sheet.

 

[Motheo]

@Peter_SSs Thanks a lot.An easy way is to set up the formula and get it working on the same sheet as the data as suggested above and then Cut that formula cell and Paste it into the other sheeControl+shift+enter, not just enter: =SUM(IF($C$3:$C$2500>=K3,IF($C$3:$C$2500<=L3,1-($B$3:$B$2500="")))) This c

Control+shift+enter, not just enter:

=SUM(IF($C$3:$C$2500>=K3,IF($C$3:$C$2500<=L3,1-($B$3:$B$2500=""))))

This counts non-blank and non-empty data cells.

This is not working for me, it returns the #VALUE! error. Any help?