Excel 2003 version of Countifs

[Mr Swift]

I have tried the suggested Excel 2003 solution to the Countifs function but
am getting stuck.

I want to count the number of tems in a spreadsheet where two predefined
conditions are met so where the month = x and where the status is "1"

My original formula is: =_xlfn.COUNTIFS('HB Appeals'!$E$2:$E$65536,"=5",'HB
Appeals'!$L$2:$L$65536,"=1") as I had it sorted on Excel 2007.

I have tried: =SUMPRODUCT("--('HB Appeals'!e2:E200,4)","--('HB
Appeals'!L2:L200,1)") as suggested but I am getting nowhere.

Please adise

 

[Mr Swift]

Thank you,

but this doesn't work for me I have included a snapshot of the data below...
I want to count the incidents where the month number is 4 and the revised
status (which is validated by a 1) is true. What am I doing wrong?


Date Rec'd Month Revision or Appeal Reason Further Info Required Nature of
Info Required Date of FI Request Revised
2.4.09 4 Revision Nil qualified - incomplete info y 1
2.4.09 4 Nil qualified - no rent liability
2.4.09 4 Backdating refusal
6.4.09 4 Appeal Non dep deduction/overpayment
7.4.09 4 Appeal Backdating refusal y 1
14.4.09 4 Backdating refusal
17.4.09 4 Backdating refusal
21.4.09 4 Termination of housing benefit
24.4.09 4 Disagrees with income used
29.4.09 4 Overpayment
5.5.09 5 Backdating refusal
8.5.09 5 Overpayment/ownership of prop
13.5.09 5 Appeal Overpayment
19.5.09 5 Non dep deduction/overpayment
20.5.09 5 Revision Nil qualified - incomplete info y 1
21.5.09 5 Termination of housing benefit
26.5.09 5 Backdating refusal
29.5.09 5 Termination of housing benefit
01.6.09 6 Termination of housing benefit
6.5.09 5 Backdating refusal
1.6.09 6 Backdating refusal
3.6.09 6 Backdating refusal

 

[T. Valko]

but this doesn't work for me

When I copy/paste your data into Excel I get a result of 2. Explain what
"doesn't work" means.

 

[JLatham]

Double check your formula, the one that T. Valko gave you should work IF your
4's (month number) are in column B and the 1's are in column L. Somehow I
suspect (based on your snapshot) that the 1's are not in column L??