| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count If
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If
Fiona,
Try this =COUNT(IF(Page1!$J$5:$J$10000=$H$1,IF(ISNUMBER(FIN D(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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If
Thanks Bob but that didn't seem to work
"Bob Phillips" wrote: Fiona, Try this =COUNT(IF(Page1!$J$5:$J$10000=$H$1,IF(ISNUMBER(FIN D(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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If
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) "Fiona" wrote in message ... Thanks Bob but that didn't seem to work "Bob Phillips" wrote: Fiona, Try this =COUNT(IF(Page1!$J$5:$J$10000=$H$1,IF(ISNUMBER(FIN D(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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If
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 D5:D5000 if it contains A10. Thanks again for your help Fiona :) "Bob Phillips" wrote: 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) "Fiona" wrote in message ... Thanks Bob but that didn't seem to work "Bob Phillips" wrote: Fiona, Try this =COUNT(IF(Page1!$J$5:$J$10000=$H$1,IF(ISNUMBER(FIN D(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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If
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" wrote in message ... 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 D5:D5000 if it contains A10. Thanks again for your help Fiona :) "Bob Phillips" wrote: 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) "Fiona" wrote in message ... Thanks Bob but that didn't seem to work "Bob Phillips" wrote: Fiona, Try this =COUNT(IF(Page1!$J$5:$J$10000=$H$1,IF(ISNUMBER(FIN D(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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count If
Thankyou, that solved it.
"Bob Phillips" wrote: 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" wrote in message ... 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 D5:D5000 if it contains A10. Thanks again for your help Fiona :) "Bob Phillips" wrote: 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) "Fiona" wrote in message ... Thanks Bob but that didn't seem to work "Bob Phillips" wrote: Fiona, Try this =COUNT(IF(Page1!$J$5:$J$10000=$H$1,IF(ISNUMBER(FIN D(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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
| Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
| Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
