#N/A! error due to complete column reference

[lite4d]

I using the below formula to count records in another sheet based on dates. I keep getting the #N/A! error because I am referencing the whole M:M column becuase more data will be added periodically. When I use a specific set of cells in M:M it works. Is ther a way around using specific cells?

=SUMPRODUCT((TEXT('CPJZ GI'!M:M,"mmm-yyyy")=TEXT(C2,"mmm-yyyy"))*('CPJZ GI'!E:E<=4))

 

[Alex Blakenburg]

Is there any reason you can't use SumIfs ? SumProduct is a terribly inefficient formula and will calculate the whole column.
A sumifs formula would look something like this:

=COUNTIFS('CPJZ GI'!M:M,">="&EOMONTH(C2,-1)+1,'CPJZ GI'!M:M,"<="&EOMONTH(C2,0),'CPJZ GI'!E:E,"<=" &4)

Easier to read if you add Line Feeds:

=COUNTIFS(
    'CPJZ GI'!M:M,">="&EOMONTH(C2,-1)+1,
    'CPJZ GI'!M:M,"<="&EOMONTH(C2,0),
    'CPJZ GI'!E:E,"<=" &4)

Please update your profile to show what version of Excel you are using as the solutions vary depending on the version.
Also the above probably doesn't need to use EOMonth twice but that depends on what value you have in C2.

 

[Michael M]

Why not simply select an arbitary cell reference, ie, M1:M10000..

 

[lite4d]

I can't select a specific number of rows because I have right now over 27K rows and more to be added periodically.

 

[Alex Blakenburg]

Did you try the Countifs formula I had in post # 2 ?

PS: An alternative to setting a number of rows is to use an Excel Table and using Table references in your formula which would automatically include any new rows you added.

 

[lite4d]

Is there any reason you can't use SumIfs ? SumProduct is a terribly inefficient formula and will calculate the whole column.
A sumifs formula would look something like this:

=COUNTIFS('CPJZ GI'!M:M,">="&EOMONTH(C2,-1)+1,'CPJZ GI'!M:M,"<="&EOMONTH(C2,0),'CPJZ GI'!E:E,"<=" &4)

Easier to read if you add Line Feeds:

=COUNTIFS(
    'CPJZ GI'!M:M,">="&EOMONTH(C2,-1)+1,
    'CPJZ GI'!M:M,"<="&EOMONTH(C2,0),
    'CPJZ GI'!E:E,"<=" &4)

Please update your profile to show what version of Excel you are using as the solutions vary depending on the version.
Also the above probably doesn't need to use EOMonth twice but that depends on what value you have in C2.

Both of those show a zero. I have to match the date that is in C2(OCT-22) to the date in M:M(mm/dd/yyyy) and then find all of those records to determine witch of those have E:E<=4.

 

[Alex Blakenburg]

Is C2 Text or a Date ? To test change the format to something else eg mm/dd/yyyy and if it doesn't change it is Text.
Do the same to Column M change to format to something different to what is displaying and tell me if it changes ?
Remember to hit Ctrl+Z to change it back each time.

Ideally show me an image of C2 which includes the Formula box and the cell in the image, then do the same for one of the cells in column M preferably using a Cell that has the Day value being > 12.

 

[lite4d]

Is C2 Text or a Date ? To test change the format to something else eg mm/dd/yyyy and if it doesn't change it is Text.
Do the same to Column M change to format to something different to what is displaying and tell me if it changes ?
Remember to hit Ctrl+Z to change it back each time.

Ideally show me an image of C2 which includes the Formula box and the cell in the image, then do the same for one of the cells in column M preferably using a Cell that has the Day value being > 12.

Holy crap.......i have working on this way to long today. when i changed it to the CORRECT cell(C3) it works! Thanks for the help anyways!

 

[Alex Blakenburg]

Haha. That will do it .
It still worth considering using CountIfs. That group of functions takes UsedRange into account while SumProduct will multiply out the entire 1 million rows.