COUNTIFS converted to 2003

**jake.masters** · 10-18-2012, 12:51 PM

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.

**dwint** · 10-18-2012, 01:15 PM

try this code

Please Login or Register  to view this content.

**jake.masters** · 10-18-2012, 01:20 PM

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.

**e.isayev** · 10-18-2012, 01:24 PM

Array function

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

**jake.masters** · 10-18-2012, 01:30 PM

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.