Countif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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

Mattymoo said:
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
Click to expand...
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 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
 
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.


Mattymoo said:
Thank you for your quick reply - I just found the left function and was
Click to expand...
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)
Click to expand...
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.
 
Back
Top