HELP! Cant figure out how to calculate number of blanks per month

**1losthuman** · 12-03-2014, 08:35 PM

Hello all..

I am a newbie, and learning from reading the forums, and visiting youtube.

I am trying to figure out how to calculate blanks (where no date is entered) versus actual dates... sum should equal amount of entries per month.
I used the following to determine total entries per month:
=SUMPRODUCT(--(TEXT(Sheet1!$B$2:$B$428,"yyyymmm")=$B$1&B2))

I cant figure out how to break it out into opens(column B) versus closed (Column C) in sheet1 per month.

I would appreciate any assistance you could provide me... Thank you.

**Debraj Roy** · 12-03-2014, 08:46 PM

Hi Martin..

Welcome to the Forum..

Lil bit confused.. regarding Open & Closed cases.. as I dont think.. Sum of Open + Closed should be same as total number of entries..

still you may get the idea how to proceed.. and idea.. of why not to use Merge in Excel..

HELP.xlsx

Formula:

Please Login or Register  to view this content.

same for "FINREPORTDATE"

PS: BTW.. (REQUESTEDDATE>0) is also not required.. in case of Month & year..

**Tony Valko** · 12-03-2014, 08:49 PM

Maybe this...

I assume "Opens" means if a date is entered in column B?

=SUMPRODUCT(--(TEXT(Sheet1!$B$2:$B$428,"yyyymmm")=$B$1&B2),--ISNUMBER(Sheet1!$B$2:$B$428))

I assume "Closed" means dates in both columns B and C?

=SUMPRODUCT(--(TEXT(Sheet1!$B$2:$B$428,"yyyymmm")=$B$1&B2),--ISNUMBER(Sheet1!$B$2:$B$428),--ISNUMBER(Sheet1!$C$2:$C$428))

**1losthuman** · 12-03-2014, 08:53 PM

WOW! I see it now... I sometimes I wish it just sank much easier... I LOVE this stuff... wish I would have started much younger in life...
Thank you very much! I do appreciate the quick response...