translating countifs function to Excel 2003

[ridgeback]

I am successfully using the following countifs function in a 2007 worksheet,
but need to give it to 2003 users, and am having a hard time translating it
to something that will work in excel 2003. In a spreadsheet sorted by
column A, the formula checks first to see if it is the first occurance of the
value in A, then for all the rows with that same value in col A within the
following 20 rows, it counts the number of occurances of "XX" in the B
column.

=IF(A4=A3,"",COUNTIFS(A4:A19,A4,B4:B19,"xx"))


For Cols A and B it will return the values in C
_A__ _B__ _C___
apple xx 2
apple
apple xx
banana xx 1
banana
peach 0
pear xx 3
pear
pear xx
pear xx
pear

How can I make this work in a 2003 spreadsheet using array formulas?

 

[Peo Sjoblom]

Maybe something like

=SUMPRODUCT(--(A4:A19=A4),--(B4:B19="xx"))


--


Regards,


Peo Sjoblom

 

[JE McGimpsey]

One way:


C4: =IF(A4=A3,"",SUMPRODUCT(--($A$1:$A$1000=A4),--($B$1:$B$1000="xx")))

 

[ridgeback]

thanks very much... it works. What does the -- do before the parenthesis
surrounding the array criteria?

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)