Countif

[Mattymoo]

Hi

My knowledge of using excel formulas is limited to basic cals, but I need to develop it quick!

I have a spreadsheet that is growing daily (currently about 350 records) and I need to count the number of entries by the first two digits of the postcode (zip code). I guess countif is the right expression to use, but how do I ask it to only look at the first two digits of the cell
Thank you for your help

 

[Frank Kabel]

Hi
some ways:
=COUNTIF(A1:A100,"12*")
if '12' are the first two digits of your zip code

or try
=SUMPRODUCT(--(LEFT(A1:A100,2)="12"))


--
Regards
Frank Kabel
Frankfurt, Germany

Hi

My knowledge of using excel formulas is limited to basic cals, but I need to develop it quick!

I have a spreadsheet that is growing daily (currently about 350

records) and I need to count the number of entries by the first two
digits of the postcode (zip code). I guess countif is the right
expression to use, but how do I ask it to only look at the first two
digits of the cell

 

[Mattymoo]

Thank you for your quick reply - I just found the left function and was trying it out (unsuccessfully!), but I don't think I explained myself very well.

I have one spreadsheet with all the postcodes listed (postcode analysis) and the source spreadsheet is the export. I'm trying to use the formula to insert the result into each postcode on the postcode analysis spreadsheet. So if the first two digits = ones on my list then count them.

Hope that makes sense

Pauline

 

[Guest]

Hi Mattymoo

You can still use Frank's formulas for this. If the postcode you want the
count of is in C2, for example, use:
=SUMPRODUCT(--(LEFT(A1:A100,2)=C2))
You can then fill this down to the rest of your list.

--
Andy.

Thank you for your quick reply - I just found the left function and was

trying it out (unsuccessfully!), but I don't think I explained myself very
well.

I have one spreadsheet with all the postcodes listed (postcode analysis)

and the source spreadsheet is the export. I'm trying to use the formula to
insert the result into each postcode on the postcode analysis spreadsheet.
So if the first two digits = ones on my list then count them.