Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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

  1. #1
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,011
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

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

    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.

  3. #3
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by shg View Post
    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.

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,011
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

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

    Post some example data?

  5. #5
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.



    Images hosted here:

    https://ibb.co/dfvua6
    https://ibb.co/dcWNTR
    https://ibb.co/eahwoR
    https://ibb.co/b2vGoR

  6. #6
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  7. #7
    Board Regular
    Join Date
    Aug 2012
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  8. #8
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by bellman101 View Post
    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

  9. #9
    Board Regular
    Join Date
    Aug 2012
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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...

  10. #10
    New Member
    Join Date
    Nov 2017
    Location
    Indiana
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by bellman101 View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •