Countifs alternative on 2003 with multiple cells and worksheets

I created the following in Excel 2007 but need to come up with a replacement formula in 2003.

=COUNTIFS('Data High'!AM2:AM999,"0",'Data High'!AN2:AN999,"Non Corp",'Data High'!AL2:AL999,"No")+COUNTIFS('Data Medium'!AI2:AI2000,"No",'Data Medium'!AJ2:AJ2000,"0",'Data Medium'!AK2:AK2000,"Non Corp")+COUNTIFS('Data Low'!AA2:AA5000,"No",'Data Low'!AB2:AB5000,"0",'Data Low'!AC2:AC5000,"Non Corp")

With this spreadsheet i pull data straight from a database and place it in to 3 separate worksheets. The first worksheet which is the summary sheet then updates all the totals of certain occurrences. The above formula is the same format for all fields in the summary with a few variations. But they all have the following in common. They draw data from all 3 worksheets and total it all up, and each countifs looks at 3 different cells.

In terms of replacing it i have tried to use something like =SUMPRODUCT(--('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp")) but don't seem to be getting it right.

Will really appreciate any suggestions.

Your suggested SUMPRODUCT should work though you don't need the double unary and you may have issues re: "0"

Re: double unary in your sample you could remove & use:

=SUMPRODUCT(('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp"))
(the multiplication of the results will coerce the Booleans)

Re: "0"
in a COUNTIF with a criteria of "0" both 0 as number and 0 as text will be included in the COUNT whereas in a SUMPRODUCT only 0 as text will be counted... thus if 'Data Low'!AB2:AB5000 contain numbers as numbers & not as text strings you won't get a result and in that case you should use:

=SUMPRODUCT(('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000=0)*('Data Low'!AC2:AC5000="Non Corp"))

If you wanted to opt for double unary approach over * method:

=SUMPRODUCT(--('Data Low'!AA2:AA5000="No"),--('Data Low'!AB2:AB5000=0),--('Data Low'!AC2:AC5000="Non Corp"))

However, what I would say is you're going to need 3 SUMPRODUCTS for each calculation... each referencing a big ish range... performance will be affected.

I would personally advocate you create a concatenation string of the fields of interest on each source sheet in a separate column, you can then use a standard COUNTIF which will be significantly more efficient... eg

'Data Low'!AZ2 = $AA2&":"&$AB2&":"&$AC2
copy down to AZ5000

Then instead of the above SUMPRODUCT you can simply use:

=COUNTIF('Data Low'!$AZ$2:$AZ$500,"No:0:Non Corp")

much, much, much more efficient and obviously compatible with all versions.