COUNTIFS formula under excel 2003

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

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

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

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

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