+ Reply to Thread
Results 1 to 9 of 9

Counting Data that meets two criteria in excel2003

  1. #1
    Registered User
    Join Date
    02-18-2011
    Location
    Boston, USA
    MS-Off Ver
    Excel 2003
    Posts
    41

    Counting Data that meets two criteria in excel2003

    I have two columns (state, date) I want to count if the state is anything but “closed” and the date is <today’s date. My results keep reading zero and I know it isn’t.

    Thanks

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting Data that meets two criteria in excel2003

    And you're calculating your results how exactly ?

    XL2003 - most likely SUMPRODUCT

    Please Login or Register  to view this content.
    use of TODAY makes the above Volatile obviously.

  3. #3
    Registered User
    Join Date
    02-18-2011
    Location
    Boston, USA
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Counting Data that meets two criteria in excel2003

    Hi
    this is the last one I tried. In this example I used a cell reference that contained todays date.
    =SUMPRODUCT((Data!AK2:AK5000="")*(Data!O2:O5000<Z44))

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting Data that meets two criteria in excel2003

    Assuming you believe the result for the above should be something other than 0 what does the below return ?

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-18-2011
    Location
    Boston, USA
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Counting Data that meets two criteria in excel2003

    It returns 3030

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting Data that meets two criteria in excel2003

    If as implied my suggestion isn't working could you please post a sample file illustrating the problem ?
    (obviously dummy any values that would be deemed confidential)

  7. #7
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Lightbulb Re: Counting Data that meets two criteria in excel2003

    If you want to count how many states closed today use dcount function

    see attached sheet

    Regards
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-18-2011
    Location
    Boston, USA
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Counting Data that meets two criteria in excel2003

    Hi,
    I have attached part of the file. What I want to do is count how many records (rows) meet the following criteria: <> closed (State in col. CN) and is < than today's date (Due date in col. O).

    Thanks
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: Counting Data that meets two criteria in excel2003

    Your data gives zero because all of your due dates are greater then today

    I have changed some dates and these are counted

    see attached sheet
    Criteria is in CS1 cell and answer in Cq1

    Regards

    Book1(3).xls
    Last edited by mahju; 03-01-2011 at 06:53 AM. Reason: Attached file updated
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1