# COUNTIFS converted to 2003

1. ## COUNTIFS converted to 2003

Hi everyone,

I am trying to convert a fairly simple formula from excel 2010 to 2003, because 2003 does not have COUNTIFS. I have tried different types of sum and sum(if array formulas, as well as multiple sumproduct formulas.

Here is the working formula:

``Please Login or Register  to view this content.``
This formula works, but strangely enough, only for half of the drop downs (even stranger, not a particular location. It works for random ones on the list, about 60% of them.). I have tried TRIM, removing hard returns, and pretty much everything else, but it just doesn't want to work. I am wondering if there is something wrong with my CSE:

``Please Login or Register  to view this content.``
Controls!K2 is the first criteria, and B3:B34 is my second criteria. The formula was dragged down.

The function works from a drop down, which selects a location. Unfortunately, I tried to create a sample sheet, but I was unable to replicate the error. I will be happy to post the sample sheet and explain what is wrong, but I am not sure if that would really be any help.

*And I realize this is a crosspost from many places. I have read between 10-15 articles today about this topic, and I cannot get a single one of them to work.

2. ## Re: COUNTIFS converted to 2003

try this code
``Please Login or Register  to view this content.``

3. ## Re: COUNTIFS converted to 2003

Thanks for the reply. Unfortunately, it returns #N/A.

I have also tried this guy, which returns #N/A as well.

``Please Login or Register  to view this content.``

4. ## Re: COUNTIFS converted to 2003

Array function

=sum(('SEPTEMBER 2012 TELEQUERY'!B:B=Controls!\$K\$2)*('SEPTEMBER 2012 TELEQUERY'!AF:AF=Controls!B3)*1)

5. ## Re: COUNTIFS converted to 2003

Thanks for the help. There were so many lines of data, I hadn't carefully looked over them all. The data itself contained a couple cells which read #N/A as a data input, and when put into a formula, excel evaluated this as an error rather than a value. I replaced them all with Not Applicable, and it worked like a charm.

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