How do I set up the formula so that the A is counted?.-Count If

Hi,

I have a small problem, I am using the formual below, the criteria in A3 is A, however the data in column in J will sometimes have date such as A/B. When this happens the A is not counted. How do I set up the formula so that the A is counted.

=COUNTIF('January Consolidation'!$J$9:$J$459,A3)

Thanks in advance for any help.

Calli.

If you use wildcards * then this will give you a count when the contents of A3 appear anywhere within the cells in the range, even amongst other text

=COUNTIF('January Consolidation'!$J$9:$J$459,"*"&A3&"*")

Thanks, it worked.

Also....

I have put what you gave me into the following formula, but it doesn't work for me.

=SUMPRODUCT(('December Consolidation'!$J$9:$J$452="*"&A13&"*")*(ISNUMBER(SEARCH("Incomplete",'December Consolidation'!$C$9:$C$452))))

Cell A13 will have a value of A and Column J will have data such as A/B

However, when i remove the wild card syntax it works. How do I make the above formula work?

Calli.

Unfortunately you can't use wildcards the same way with a comparison operator like =, try using another ISNUMBER/SEARCH like this

=SUMPRODUCT(ISNUMBER(SEARCH(A13,'December Consolidation'!$J$9:$J$452))*ISNUMBER(SEARCH( "Incomplete",'December Consolidation'!$C$9:$C$452)))

Exactly, what is the difference between the two besides the syntax,

=SUMPRODUCT(ISNUMBER(SEARCH(A13,'December Consolidation'!$J$9:$J$452))*ISNUMBER(SEARCH( "Incomplete",'December Consolidation'!$C$9:$C$452)))

And....

=SUMPRODUCT(('December Consolidation'!$J$9:$J$452=A13)*(ISNUMBER(SEARCH("Incomplete",'December Consolidation'!$C$9:$C$452))))