Countif

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mattymoo - 22 Jul 2004 06:53 GMT

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

Reply to this Message

Frank Kabel - 22 Jul 2004 07:02 GMT

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

Signature

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

> Thank you for your help

Reply to this Message

Mattymoo - 22 Jul 2004 07:16 GMT

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
> some ways:
[quoted text clipped - 20 lines]
> digits of the cell
> > Thank you for your help

Reply to this Message

- 22 Jul 2004 16:11 GMT

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.

Signature

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.

> Hope that makes sense
>
[quoted text clipped - 24 lines]
> > digits of the cell
> > > Thank you for your help

Reply to this Message