Excel 2007 : Counting a Date Range + blank cells

**Rino468** · 11-01-2010, 01:53 PM

Hi again,

Not sure if there is a way to do this..
I need to count a certain date range that has another column with blank cells..

Column A = Date Range / Column B = Cells that need to be counted if Blank.

So it looks like this and for this example I will be counting October...

Column A Column B

10/25/2010 DATA
10/26/2010
9/10/2010 DATA
10/15/2010 DATA
10/4/2010

---------

So obviously the Result I need is 2.

Thanks ahead of time!

**NBVC** · 11-01-2010, 02:13 PM

Try:

=SUMPRODUCT(--(MONTH($A$2:$A$6)=10),--($B$2:$B$6=""))

or if year is also important...

=SUMPRODUCT(--(TEXT($A$2:$A$6,"mmm-yy")="Oct-10"),--($B$2:$B$6=""))

adjust ranges to suit your data.

**Rino468** · 11-01-2010, 02:37 PM

Awesome!! This works perfect...

One last thing --

If I wanted to use this formula on a "summary" type page.. and I needed to reference a different worksheet (lets call this WorksheetA) would the formula look something like...

=SUMPRODUCT(--('WorksheetA'!TEXT($A$9:$A$13,"mmm-yy")="Oct-10"),--('WorksheetA'!$Q$9:$Q$13=""))

Its not really working for me, so what am I missing?

**NBVC** · 11-01-2010, 02:40 PM

The sheet name always immediately prefixes the range, not the function....

=SUMPRODUCT(--(TEXT('WorksheetA'!$A$9:$A$13,"mmm-yy")="Oct-10"),--('WorksheetA'!$Q$9:$Q$13=""))