First & Subsequent Occurrences

[blakeboyuk]

Good Day

Please let me begin with an apology for my lack of knowledge and understanding regarding MS Excel. Now I will explain my problem as best I can.

I have a list of dates (column A) and UK post codes (column B) in a spreadsheet. In column C I need to put a charge for a delivery service however, the charge for the first package (or line) delivered is £11.77 and any subsequent package (line) is £4.71.

What I would love is to find a formula to be used in column C which applies the £11.77 charge for the first occurrence of the postcode and then £4.71 if this shows as a duplicate. The additional complexity is that the formula would need to factor in the date as its likely that postcodes can reoccur on multiple dates but the £11.77 charge would need to apply on any new date. I hope that makes sense.

Many thanks for your help

David

 

[Fluff]

Hi & welcome to MrExcel.
How about
=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,11.77,4.71)

 

[blakeboyuk]

Fantastic, thank you Fluff. I had a feeling an IF and COUNTIF was needed but couldn't see through the mist in my mind.

Thanks so much for your help.

 

[Fluff]

You're welcome & thanks for the feedback