PDA

View Full Version : Solved: COUNTIF

zoom38
02-22-2006, 12:10 AM
Im trying to use the countif function to count the number of cells with "A" or "A*".My formula looks like this: COUNTIF(C7:C20,"A") + COUNTIF(C7:C20,"A*).
The problem here is that when I use "A*" in the formula. It doubles the result. Ex. if "A" is in 4 cells the result is 8. Interestingly if I hit the = sign next to the formula window(not sure what its called) it displays:

COUNTIF
Range C\$7:C\$20 = {0;"A";0;"A";0;0;"B".
Criteria "A*" = "A*"
= 4

Formula Result = 8

Why does it take the correct answer 4 and multiply by 2 to get the formula result of 8? Same thing occurs with other results. 5 Formula Resut = 10, 3 Formula Result = 6 etc.

Now if I have "A*" in one of the range cells and "A" in three others I get:

COUNTIF
Range C\$7:C\$20 = {0;"A";0;"A";0;0;"B".
Criteria "A*" = "A*"
= 4

Formula Result = 7

Anyone out there know why this is happening?

Thanks
Gary

TonyJollans
02-22-2006, 12:43 AM
Your problem is that "A*" means "anything beginning with A".

To actually check for an asterisk instead of "anything" use "~*", thus ..

COUNTIF(C7:C20,"A~*")

zoom38
02-22-2006, 02:11 AM
Thanks Tony, works great.