Filtering an array based on month.

[FrancisM]

I have 3 different areas of a worksheet allowing data entry or doing calculations.

The first area is cell rang C7:F7. C7 is a date entry field, D7 is a size entry field. I.e. 6m,6W,…… E7 is a quantity entry field. F7 is populated with this formula; =SUM(COUNTIFS(D7,{"6M","6W","6.5M","6.5W","7M","7W","7.5M","7.5W","8M","8W","8.5M","8.5W","9M","9W","9.5M","9.5W","10M","10W","10.5M","11M","11.5M","12M","13M","14M"}))*E7 If the entry in D7 matches any of the sizes above it calculates number of that size based on E7.

The second area is Range i7:j7. I7 is populated with this formula =IF(C7="",0,MONTH(C7)), & is based on cell C7 (date entry). J7 IS populated with =COUNTIF(D7,"6M")*F7. It calculates the quantity of a size.

The third area is cell AJ7, &t is populated with an array. =SUM(COUNTIFS(J7,{"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29",
"30","31","32","33","34","35","36","37","38","39","40"}))*J7

If any of the numbers match J7 (size quantity) it calculates the quantity.

At present it doesn’t filter for month. I am trying to include a month filter in the array. So if the month in cell I7 is 10, the array returns the appropriate quantity. If month in cell I7 is not 10, the array returns 0. I have tried various filters, but I have gotten various error messages. What is the best way to filter the array by month?

 

[jjasmith4]

In I7, rather than the condition C7="" use ISBLANK(C7). C7 is a number, because dates are numbers, so it will never be a string, even a null string.

 

[FrancisM]

thanks. You mean it was that Simple?

 

[FrancisM]

Something strange happened. The formula worked as expected in column Aj7, however when I copied the formula to the next column Ak. the same vales were also copied. Did I do something wrong?

 

[FrancisM]

Problem solved. A few cells in the first grouping were pointing to the wrong cells.

 

[FrancisM]

I spoke too soon, however I did find out that the problem is, no month filter. A filter needs to be added to this formula =SUM(COUNTIFS(J7,{"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29",
"30","31","32","33","34","35","36","37","38","39","40"}))*E7.
If another month is selected, it still records the amount in Nov (10).
Does anyone have suggestions?

 

[FrancisM]

It appears that I am required to use helper cells. Is there workaround to that? the other question, is placement of the filter in the above array.

 

[FrancisM]

Problem solved.
=COUNTIFS($I$7:$I$7,"=10",$D$7, "=6M")*F7