#value! for the formula countif

**chinsian.chen** · 05-27-2009, 04:55 AM

hi, good days,

i face a big problem here.

i had use the formula countif but if the value is too big or too long it come out #VALUE! .i attach the attachment here. u may refer.

please help...

**zbor** · 05-27-2009, 04:57 AM

You have space in number in D3.. remove it...

1.23456789012345E+99123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678123123456789 1234567891332135454848474

**chinsian.chen** · 05-27-2009, 05:41 AM

if don't hv space, also same. hv mistakes.
but my data has many spaces and not only integer.

can help?

**DonkeyOte** · 05-27-2009, 05:44 AM

COUNTIF criteria value limited to 255 characters

**chinsian.chen** · 05-27-2009, 05:48 AM

Originally Posted by DonkeyOte

COUNTIF criteria value limited to 255 characters

thanks... i get your answer. if more than that, i need to search for other solutions.
hv any idea of it?

**DonkeyOte** · 05-27-2009, 05:50 AM

You can use SUMPRODUCT but it is very expensive performance wise

E3: =SUMPRODUCT(--($D$3:$D$5=$D3))

Pre XL2007 you can not use entire column references (ie D:D will generate a #NUM! error) ... that said you should strive to keep range sizes to a minimum when using any Array formula (this includes Sumproduct) - regardless.