Countif Troubles

[lmeg]

I'm just trying to do a countif to see how many times certain postcodes occur on my spreadsheet.

I'm sure the function is correct i.e. =countif(B2:B234,"SN15") but I am not getting the correct results.

My colleagues have checked the formula and it seems ok so we think there might be something wrong with format of the data. I have checked the format of the cells which is on general. I have copied and pasted the data to another sheet but it still doesn't work.

Any ideas?

Laura

[Dav]

the formula will count each occurence of SN15 in the cells, however this has to be the full cell contents

If the cell is space SN15 or trailing spaces or part of a greater string it will not work

what does the following return entered as an array (shift control held down then enter)

=SUM(IF(ISERROR(SEARCH("sn15",B2:B234)),0,1))

or if it is spaces before or after, try the following just entered with a return

=SUMPRODUCT((TRIM(B2:B234)="SN15")*1)

Let me know how it goes

Regards

Dav

[oldchippy]

I'm just trying to do a countif to see how many times certain postcodes occur on my spreadsheet.

I'm sure the function is correct i.e. =countif(B2:B234,"SN15") but I am not getting the correct results.

My colleagues have checked the formula and it seems ok so we think there might be something wrong with format of the data. I have checked the format of the cells which is on general. I have copied and pasted the data to another sheet but it still doesn't work.

Any ideas?

Laura

Hi Laura,

The formula is correct, so it must be the data, is SN been put in as 5N, try this formula in C2 and copy down

=LEFT(B2,1)*1

See if any 5's appear

oldchippy