Some cells work with formula, some do not, and I can't figure out why

[KGIL789]

Hi everyone!

I am making a tracking database for work for monthly/annual reporting and for the life of me cannot figure out why some cells get counted and some do not. They are written the same way - the only thing that changes is what sheet is being referred to and what date range is being referred to.

The formula I'm using is this: =COUNTIFS(Wells!$AA$3:$AA$2002,">=1/1/2017",Wells!$AA$3:$AA$2002,"<=1/31/2017")

That doesn't count for some reason.

BUT this one does? =COUNTIFS(Wells!$AC$3:$AC$2002,">=1/1/2017",Wells!$AC$3:$AC$2002,"<=1/31/2017")

One column has a date entered when a well permit is issued (non working one). The other is when we pull the sample (this one gets counted). This is set up nearly the exact same way for all our departments (there's a column for address for the environmental dept but not for vital records, etc).

I've changed all the cell formats to both short date and long date as well as text or general and nothing changes. I'm entering today's date to test (11/20/2017). All these cells are hand entered, there are no formulas apart from the counting. Nothing was counted when I used Excel's DATE function either.

Help, please? I have Office 2016.

 

[bellman101]

Hi,

So Excel is definitely seeing them as dates then.

Just looked at your screenshot again, the second date in the =COUNTIFS that is visible in the formula bar is not in the format "d/m/yyyy" as it is in your post. it is in the format "d, m, yyyy". This won't work the same.

I would suggest changing the formula as follows:

=COUNTIFS(Wells!$AC$3:$AC$2002,">=1/"&A3&"/"&LEFT($A$1,4),Wells!$AC$3:$AC$2002,"<=" & EOMONTH("1/"&A3&"/"&LEFT($A$1,4),0))

You can then just drag the formula down.

Adjust as follows:
Change both instances of A1, to whichever cell contains the text "2017 DATA"
Change both instances of A3, to whichever cell "JANUARY" is in.

Ensure dollars remain in A1, and no dollars in A3.

Use the same formula for assessing column AA amended accordingly.

Not sure if this may solve the issue, but worth doing either way.

Cheers
JB

 

[KGIL789]

Hi,

So Excel is definitely seeing them as dates then.

Just looked at your screenshot again, the second date in the =COUNTIFS that is visible in the formula bar is not in the format "d/m/yyyy" as it is in your post. it is in the format "d, m, yyyy". This won't work the same.

I would suggest changing the formula as follows:

=COUNTIFS(Wells!$AC$3:$AC$2002,">=1/"&A3&"/"&LEFT($A$1,4),Wells!$AC$3:$AC$2002,"<=" & EOMONTH("1/"&A3&"/"&LEFT($A$1,4),0))

You can then just drag the formula down.

Adjust as follows:
Change both instances of A1, to whichever cell contains the text "2017 DATA"
Change both instances of A3, to whichever cell "JANUARY" is in.

Ensure dollars remain in A1, and no dollars in A3.

Use the same formula for assessing column AA amended accordingly.

Not sure if this may solve the issue, but worth doing either way.

Cheers
JB

I missed a cell when rewriting it for the eighth time. I think I have an eye twitch. This database is driving me nuts.

So in essence, instead of including the dates, reference back to a cell and have Excel calculate end of month? I assume I can remove the left fn and just put in 2017 etc for the table titles?

(Add-in note: this database will be used by everyone in the office, including those who had no idea Excel was anything more than a fancy table-maker and actually calculates stuff, so the less stuff they can break by entering in data on the appropriate sheets, the better, hence the drop down menus. I'm hoping I can lock specific sheets so they can view the tables/charts, but not change anything on those sheets.)

 

[bellman101]

That would be my method I think, and yes, '2017' will do exactly the same there. The LEFT() would just reduce the number of changes required at New Year, but a CTRL+H in formulas should work just as well!

One final note, it looks like you fields are only date fields, but if you ever use this with date & time fields, be sure to say less than 1st of next month. Several ways of getting there. easiest just to add '+1' after the EOMONTH function.

"<=31/JAN/2017" will not include "31/JAN/2017 09:30:00", as the actual value of just the date is 42766, whereas the datetime is 42766.4-ish, so greater than the date on its own.

 

[KGIL789]

I actually have tables set up for the next five years. Hopefully at that point I've convinced them to actually get tracking software, since we're also adding ordinances left and right that will require more permits & tracking (and more staff, but that's not that likely to happen). It's been a few years since I had to do anything exciting with Excel and it turns out I've forgotten a decent sized chunk. I remember just enough to be dangerous in all the wrong ways.

Good to know on the times!

Blocked off this morning to fix the cells with the method you've put above.

 

[bellman101]

Haha, well good luck.

Give me a shout if you get stuck

 

[KGIL789]

Haha, well good luck.

Give me a shout if you get stuck

Woohoo! It worked! Thanks!

 

[bellman101]

Great to hear!

You're very welcome