SUMPRODUCT to ignore blank cells when searching dates

[Garry1965]

I have been stuck on a formula for a couple of days now. Can anyone help me please?

On one sheet I have a heap of data for industrial equipment. This includes a column of dates for inspections.
On the other sheet I am trying to build a ready reckoner for the end user to see at a glance what is happening at any chosen time.

My problem is I am trying to get a formula to show how many dates in the date column occur in any given month. So if I input January, I can see how many dates are for January; input February and see the number of dates for February and so on.
The formula I have in R8 that nearly works is;

=IF(R7>="0",SUMPRODUCT((MONTH(Equipment!K7:K25)=T7)*1),"")

I currently have ‘dummy data’ on the Equipment sheet. I have input dummy data in rows 7–25 as a test whilst I am still building the tool.
In cell R7 I have a dropdown list to select the desired month to look at.
I have a helper cell in T7 to change the searched month in the formula (January=1, February =2 etc.)
It works fine until I change the formula to look further that the current ‘data’.

I need the working version to look further down the column, at least (K7:K1000)
I.E., =IF(R7>="0",SUMPRODUCT((MONTH(Equipment!K7:K1000)=T7)*1),"")
However because there are blank cells in the date column, R8 shows: #VALUE!

How can I get this to look to K1000, ignoring the blank cells, and return a simple number of times each month occurs in the column?

This is driving me nuts and I would be very grateful for any advice as to how I can fix this.
Thanks.

 

[Noodleski]

What happens when you enter following formula:

SUM(IF(MONTH(Equipment!K7:K1000)=$T$7,1,0))

Confirm this formula using ctrl+shift+enter.
If you do it right, there will be curled brackets around the formula. {formula}

 

[Marcelo Branco]

Try this array formula
=IF(R7>=0,SUM(IF(ISNUMBER(Equipment!K7:K1000),IF(MONTH(Equipment!K7:K1000)=T7,1))),"")

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Garry1965]

What happens when you enter following formula:

SUM(IF(MONTH(Equipment!K7:K1000)=$T$7,1,0))

Confirm this formula using ctrl+shift+enter.
If you do it right, there will be curled brackets around the formula. {formula}

Hi,
I just tried your formula and unfortunately the result was the same, #VALUE!
Cheers

 

[Garry1965]

Thanks Marcelo

Works perfectly now.
My only query now is, What does the Ctrl+Shift+Enter resulting in the entire formula being inside curly brackets mean?
Why did I need to do that?

Anyway, I'm so glad it worked, I can move on to the next issue now.

Thanks again
G

 

[Marcelo Branco]

Thanks Marcelo

Works perfectly now.
My only query now is, What does the Ctrl+Shift+Enter resulting in the entire formula being inside curly brackets mean?
Why did I need to do that?

Anyway, I'm so glad it worked, I can move on to the next issue now.

Thanks again
G

You are welcome. Thanks for the feedback.

About array formulas take a look at
Array Formulas
Excel: Introduction to Array Formulas - Xtreme Visual Basic Talk

M.

 

[Noodleski]

Hi,
I just tried your formula and unfortunately the result was the same, #VALUE!
Cheers

Hm, worked when I tried it. But glad you got a solution for your problem anyway.