+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS formula under excel 2003

  1. #1
    Registered User
    Join Date
    04-04-2010
    Location
    Timisoara, Romania
    MS-Off Ver
    Excel 2003
    Posts
    4

    COUNTIFS formula under excel 2003

    Hi everyone,

    I have a sheet in EXCEL (actually a .CSV file - but if needed i can save it under a .XLS file) with this data:
    - on one column I have some ID numbers
    - on one column I have dates corresponding to those ID numbers (like 1.04.2010 or any other date format)
    - on another column I have some codes (like a badge number that contains characters and numbers (ex. AN12145)

    so..one row could look like this:
    ID no .... |*****date**** | badge
    32654400 | 1.04.2010 | AN12145

    I need a formula that can calculate the number of each dates that a badge has so I can populate the following table:
    badge/date| 1.04.2010 | 2.04.2010 | 3.04.2010 | ...and so on
    AN12145 | ............... |............... |................|
    AN12146 | ............... |................| ...............|
    AN12147 |.................|................|................|

    I cannot use filters because I need the source information and this table in separate files. The information table will update very often. In EXCEL 2007 i found the "COUNTIFS" formula that is exactly what I need. Works perfectly and I was able to adapt it to my case (separate files) but unfortunately I need this to work in EXCEL 2003 that does not support this formula.

    Any help is greatly appreciated. Thanks
    Last edited by mihait85; 04-05-2010 at 04:17 AM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: COUNTIFS formula under excel 2003

    Hello mihai, thanks for changing the title.

    You can use Sumproduct() like this:

    =sumproduct(--($B$2:$B$1000=F$1),--($C$2:$C$1000=$E2))

    copy down and across.

    With your data columns in A, B and C and the summary table starting in column E.

    If that does not help, post a workbook with a small data sample.

  3. #3
    Registered User
    Join Date
    04-04-2010
    Location
    Timisoara, Romania
    MS-Off Ver
    Excel 2003
    Posts
    4

    Thumbs up Re: COUNTIFS formula under excel 2003

    teylyn, works perfectly and I was able to make it work with a .csv file. Till now I don't know why it wouldn't work.

    Thanks a lot for your idea. See you

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: COUNTIFS formula under excel 2003

    mihai, glad it works for you.

    COUNTIFS is a new function introduced with Excel 2007. It can count instances of data based on several conditions.

    For earlier versions of Excel, the SUMPRODUCT() workaround is the only way to achieve the same thing.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ 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