Counting Months where range is Dynamic

[Kemidan2014]

I have a spreadsheet that Access will be exporting data into for graphing.

I need to be able to set up the formula so the range is dynamic, When i use "=SUMPRODUCT(--((MONTH(NONCTab_Query!B2:B7)=Calculations!E2)))" it calculates correctly. where calculations E2 is just a cell with a number corresponding to the month. however the RANGE "NONCTab Query!B2:B7" will be dynamic and grow overtime. and simply expanding the range adds to the result as apparently Null cells are considered "True"?

My dates are formatted as mm/dd/yyyy

how can i change the function so it will add up correctly and allow me to pad the range large enough to cover expected range growth and ignore Null

Keep in mind i will also have to include ADDITIONAL criteria for further counting for other Graphs have yet to create.

all of the internet examples and helpful videos only handle situations of fixed ranges.

I also tried CountIFS but just yielded 0 as a result.

 

[depcdivr]

Have you tried using Loop in VBA to do you calculations. You can use a row count to determine what your range will be and then a loop for your calculation. Then add your results back to your spreadsheet. This way it will automatically calculate your new range every time you run it and you never have to go in to extend the range.

 

[Fluff]

How about

=SUMPRODUCT((MONTH(NONCTab_Query!B2:B100)=E2)*(NONCTab_Query!B2:B100<>""))

 

[Kemidan2014]

FLuff your formula worked. I was also able to add more criteria for another column of data which is still adding correctly. THank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Georgiboy]

An option to count the entire column and not slow the formula down:

=LET(f,MONTH(FILTER(NONCTab_Query!B:B,(NONCTab_Query!B:B<>""))),COUNT(FILTER(f,f=E2)))