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

KGIL789

New Member
Joined
Nov 20, 2017
Messages
14
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Those two formulas are identical.

I've changed all the cell formats to both short date and long date as well as text or general and nothing changes.
You mean the appearnce doesn't change when you change the formatting? if so, then the cells are text, not dates.

Format the cells are general, select the column, do Data > Text to columns, Finish. Now they should like like numbers.

Now change to the format you want and proceed.
 
Upvote 0
Those two formulas are identical.


You mean the appearnce doesn't change when you change the formatting? if so, then the cells are text, not dates.

Format the cells are general, select the column, do Data > Text to columns, Finish. Now they should like like numbers.

Now change to the format you want and proceed.

The appearance changes, but nothing is counted still.
 
Upvote 0
Some screenshots - all data will be entered in on the color-coded sheets. The non-colorful sheets will be generated based off the colorful sheets.
dfvua6
dfvua6

dfvua6


Images hosted here:

https://ibb.co/dfvua6
https://ibb.co/dcWNTR
https://ibb.co/eahwoR
https://ibb.co/b2vGoR
 
Upvote 0
This database will be implemented either late 2017 or start of 2018 - right now there's apparently no tracking going on, which is what I've been asked to fix. I've been entering in test data of random dates that should show up but aren't, there's no actual real data that I'm working with.
 
Upvote 0
Hi KGIL,

Can you post a screenshot where we can see a populated date?

What happens if you put an autofilter across row 2? what is in the dropdown for Column AA?

Cheers
JB
 
Upvote 0
Hi KGIL,

Can you post a screenshot where we can see a populated date?

What happens if you put an autofilter across row 2? what is in the dropdown for Column AA?

Cheers
JB


I'm not sure why I would filter row 2 - in several sheets that's my headers. If I filter a column that has a date in it: https://ibb.co/fqvfHm

AA doesn't have drop downs in any of my sheets.

Some of my columns do have drop downs - "routine", "complaint", (other types of inspections), and for birth/death it's "male" v "female". See here: https://ibb.co/jOchV6 and also here https://ibb.co/dN0i3R

For wells/septics there is no drop down menu. Here are all the columns for wells with fake data entered in row 1: https://ibb.co/gPMsV6
 
Upvote 0
No, I'm not suggesting you use autofilter as a feature of your file. Just that you temporarily apply one, and then look in the autofilter drop down list for column AA. if they are being seen as dates, then it should give years, which expand to months, and then days. If it does not do this, then for some reason they are not seen as dates. Also, in one of your screen shots above you were looking for dates <= 31st Feb, which I think will resolve in an error. Try < 1st March instead...
 
Upvote 0
No, I'm not suggesting you use autofilter as a feature of your file. Just that you temporarily apply one, and then look in the autofilter drop down list for column AA. if they are being seen as dates, then it should give years, which expand to months, and then days. If it does not do this, then for some reason they are not seen as dates. Also, in one of your screen shots above you were looking for dates <= 31st Feb, which I think will resolve in an error. Try < 1st March instead...

The columns that should have dates are all appearing with year, then month, then day when I apply the filter.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top