General discussion

  • Creator
    Topic
  • #2195779

    Excel question

    Locked

    by ronan.anglin ·

    In an excel file, i have 2 worksheets called “A” and “B”. “A” has a load of data on the sheet with a filter turned on. B is a blank worksheet. In “A” i filter it down for things that happened between 2 dates, say >10-1-06 and < 10-2-06 since date is a field. This reduces the data from a 1000 lines to a hundred lines. Can I automatically format a cell in the second work sheet to count the number of lines between those dates. I assume i'll be putting in some kind of fomula into the formula bar? Maybe something like....=COUNT 'A!' where Date >10-1-06 & < 10-2-06. Can I do the whole thing automatically without having to filter A?

All Comments

  • Author
    Replies
    • #3163725

      Reply To: Excel question

      by rdk55 ·

      In reply to Excel question

      Give this formula a try. No prior filtering should be required…

      =COUNTIF(RANGE,”>=”&DATEVALUE(“10-1-06”))-COUNTIF(RANGE,”>”&DATEVALUE(“10-2-06”))

      Replace both occurances of “RANGE” with your actual range on your spreadsheet.

      You may have to change the way the datevalues are entered as well depending on your regional settings. (On my system, this works with DATEVALUE(“01/10/2006”).

      R.K.

Viewing 0 reply threads