Count If

[Fiona]

Hi
I'm trying to get the following formula to work:
COUNT(IF(Page1!$J$5:$J$10000,$H$1,IF(Page1!$D$5:$D$5000,"*"&A10&"*",0)))

But it isn't. I'm trying to count the cells on page 1 that have H in
coloumn J, but also contain A10 in coloumn D.

I've done th {} at each end but it doeasn't work.

Any help would be very much appreciated.

Thanks
Fiona

 

[Bob Phillips]

Fiona,

Try this

=COUNT(IF(Page1!$J$5:$J$10000=$H$1,IF(ISNUMBER(FIND(A10,Page1!$D$5:$D$5000)),1)))

still array entered.

--
HTH

Bob

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

 

[Fiona]

Thanks Bob but that didn't seem to work

Fiona,

Try this

=COUNT(IF(Page1!$J$5:$J$10000=$H$1,IF(ISNUMBER(FIND(A10,Page1!$D$5:$D$5000)),1)))

still array entered.

--
HTH

Bob

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

 

[Bob Phillips]

Care to elucidate, it worked in my test.

--
HTH

Bob

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

 

[Don Guillett]

In your original post you saidIf you actually did that, do this:
remove the {} and then hold down the ctrl + shift keys and touch the enter
key>let go.
Now you should see the {} and the formula should work.

 

[Fiona]

Sorry Bob, I've been looking at it again and the problem is that in my
previous formula I used the 'contains' feature ("*"&A10&"*") but with your
formula the match needs to be exact. I've tried to enter the 'contains' bit
but this does not give the correct result.

I want to count D55000 if it contains A10.

Thanks again for your help

Fiona

 

[Bob Phillips]

I did read your post and that is why I used Find, so I cater for contains.

It might be a case problem, try changing FIND to SEARCH.

--
HTH

Bob

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

 

[Fiona]

Thankyou, that solved it.

I did read your post and that is why I used Find, so I cater for contains.

It might be a case problem, try changing FIND to SEARCH.

--
HTH

Bob

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