'If' formula for pricing

**Cms040889** · 12-21-2011, 12:10 PM

Hi,

I need help on my spreadsheet.

If the there is only one postcode the price for that is £21.50.
If there are two postcodes the same, the first need to be £20 and the second £1.50.
If there are three postcodes the same, the first is £20 and the second is 0.75p and the third is 0.75p.
If there are four postcodes the same, the first is £20 and the second is 0.75p, the third is 0.75p and the fourth is £9.
If there are five postcodes the same, the first is £20 and the second is 0.75p, the third is 0.75p, the fourth is £9 and the fifth is £9.
If there are six postcodes the same, the first is £20 and the second is 0.75p, the third is 0.75p the fourth is £9, the fifth is £9 and the sixth is £9.
And so on till 10 postcodes

Is there a formula I could use?

Thanks

**Cms040889** · 12-21-2011, 12:47 PM

Please advise if this is suitable

**daddylonglegs** · 12-21-2011, 06:41 PM

Yes, the title is fine, thanks.

Can you describe how your data is laid out. Is each postcode in a separate cell? What if you have 5 of one postcode and 5 of another, do you want to calculate a single price for all 10?

**Cms040889** · 12-22-2011, 03:21 AM

Hi

The postcodes are all in single cells

Please see below for example

Ab1 2cd £21.50
Bc1 2de £20
Bc1 2de £1.50
Cd1 2ef £20
Cd1 2ef 75p
Cd1 2ef 75p
De1 2fg £20
De1 2fg 75p
De1 2fg 75p
De1 2fg £9
Etc

I will need it different for different postcodes as per above

Regards

**daddylonglegs** · 12-22-2011, 08:37 AM

If postcodes are in A2 down try this formula in B2 down

=CHOOSE(MIN(COUNTIF(A$2:A2,A2),11),IF(COUNTIF(A:A,A2)=1,21.5,20), IF(COUNTIF(A:A,A2)=2,1.5,0.75),0.75,9,9,9,9,9,9,9,0)

see attached

I assume that after 10 the cost is zero, if not then change the red zero as required