translating countifs function to Excel 2003
 

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?

translating countifs function to Excel 2003
 

Maybe something like

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


--


Regards,


Peo Sjoblom


"ridgeback" wrote in message
...
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?

translating countifs function to Excel 2003
 

One way:


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

In article ,
ridgeback wrote:

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?

translating countifs function to Excel 2003
 

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


"Peo Sjoblom" wrote:

Maybe something like

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


--


Regards,


Peo Sjoblom


"ridgeback" wrote in message
...
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?

translating countifs function to Excel 2003
 

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)



"ridgeback" wrote in message
...

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


"Peo Sjoblom" wrote:

Maybe something like

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


--


Regards,


Peo Sjoblom


"ridgeback" wrote in message
...
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?