I want to analyse data from alternate columns using the countif function and
even though clicking in the formula bar shows exactly the columns I want, the
analysis picks up the columns in between too. I've solved it for the moment
by changing the codes for the second data set but that seems clumsy and excel
looks as though it should do the job. Any hints please?

Juniper wrote:
I want to analyse data from alternate columns using the countif function and
even though clicking in the formula bar shows exactly the columns I want, the
analysis picks up the columns in between too. I've solved it for the moment
by changing the codes for the second data set but that seems clumsy and excel
looks as though it should do the job. Any hints please?

if the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then for
even columns

=ArrayCountIf(ArrayAlternates(D1:G4,FALSE,FALSE),3 ) and for odd columns

=ArrayCountIf(ArrayAlternates(D1:G4,,FALSE),3)

Alan Beban

Thanks Alan, I'll give that a go

"Alan Beban" wrote:

Juniper wrote:
I want to analyse data from alternate columns using the countif function and
even though clicking in the formula bar shows exactly the columns I want, the
analysis picks up the columns in between too. I've solved it for the moment
by changing the codes for the second data set but that seems clumsy and excel
looks as though it should do the job. Any hints please?

if the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then for
even columns

=ArrayCountIf(ArrayAlternates(D1:G4,FALSE,FALSE),3 ) and for odd columns

=ArrayCountIf(ArrayAlternates(D1:G4,,FALSE),3)

Alan Beban

Either my workbook won't accept the functions or I'm not accessing them
properly. Either way, we're no go for now but many thanks anyway.
Suzanne

"Alan Beban" wrote:

Juniper wrote:
I want to analyse data from alternate columns using the countif function and
even though clicking in the formula bar shows exactly the columns I want, the
analysis picks up the columns in between too. I've solved it for the moment
by changing the codes for the second data set but that seems clumsy and excel
looks as though it should do the job. Any hints please?

if the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then for
even columns

=ArrayCountIf(ArrayAlternates(D1:G4,FALSE,FALSE),3 ) and for odd columns

=ArrayCountIf(ArrayAlternates(D1:G4,,FALSE),3)

Alan Beban