Sumproduct

[KennyG]

I have the following within a spreadsheet, it counts the cells within a given date range,


=SUMPRODUCT(--('ACTION PLAN'!E14:E4000="PMA4"),--('ACTION PLAN'!N14:N4000>='ACTION PLAN'!$N$4),--('ACTION PLAN'!N14:N4000<='ACTION PLAN'!$N$5)*ISNUMBER('ACTION PLAN'!N14:N4000))


PMA4 is a machine

N4:N4000 is a range with dates or no dates

$N$4 and $N$5 are the dates it works between

I need to count all the blanks within the same range. I have tried various options but cannot crack it!!!

Your help would be much appreciated

KennyG

Best to ask the experts

[cmyers]

=COUNTBLANK(N14:N4000)

Hope that helps!

[ChemistB]

=SUMPRODUCT(--('ACTION PLAN'!E14:E4000="PMA4"),--(ISBLANK(N14:N4000)))

Does that work for you?

[KennyG]

It works as shown but I cannot get ot to work when I add in the date range, ie where i use the $n$4 and $n$5

Any more ideas ??

many thanks

[ChemistB]

If there is a date in Column N, then there won't be a blank in column N. Now I'm confused.

[KennyG]

Some cells have dates and some are blank until a user has input a date. The date range is from N14:N4000.

I input a start date into N4 and end date into N5 to search between 2 dates.

Hope this helps to clarify my previous poor explanation,

many thanks

[daddylonglegs]

Do you mean that the dates are sorted? Do you want to count blanks, therefore, between the first instance of N4 and the last instance of N5 (can they appear mutiple times?). What happens if N4 and N5 dates aren't in the range, what should happen then?

[KennyG]

Hi Daddylonglegs,

Rows 1:14 are the top part of the form, with row 13 filtered along the different headings

The range I am counting is N14:N4000.

I have a start date in N4 and an end date in N5 - it is only by coincedence that these are in column N as I calcualte many other columns from these to dates.

The users input dates in the range N14:N4000, some have dates input and I can count them as per the formula in my first post.

I also need to count the cells that have no input, so are blank. ChemistB formula works on its own but I cannot get ot to work with the date range thye same as when I count when dates have been entered.

Does this help ??

many thanks for the assistance,


KennyG

[daddylonglegs]

I'm with ChemistB, which blanks are you counting? You say it's based on a date range but do you mean a date range in another column? If not then how can a blank be in a range......or do you mean that it's physically located between the start and end dates of your range?

[KennyG]

Daddylonglegs,
You have put me on the correct train of thought!!!! Yes, in this instance the date range is in another column B. I will then count the blanks in column N using the ISBLANK option.


I think that I may now have cracked it, I will check in the morning and provide feedback.

Once again, many thanks for everyones input, it is much appreciated.

Kenny G