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

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.

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