Counting multiple instances of same name and calculating charges that exceeding 5 postings

I have names that appear in column B that may appear once or 30 to 40 times. Each of those names has a charge assoiated with it (column C). A contract was negotiated that states the charges will remain consistent up to five charges, and after that the customer (column B) will only get charged $5.00 per entry.

I need to count how many times the customer appears in column B, calculate how much they were charged, then figure out how much they were overcharged.

Use COUNTIF to count them and SUMIF to work out how much they've been charged.

Or maybe use a Pivot Table.

Regards, TMS

The problem I have with that is, the same answer will appear next to the name each time it is listed. I would like the first five instances of the names to read "OK", or something like that and additional instances to show the overcharge.

If you want to print OK/Overcharge in new column then use.

=IF(COUNTIF(B$2:B2,B2)<=5,"OK",MIN(C2,5))

copied down.

You can also use

=IF(COUNTIF(B$2:B2,B2)<=5,C2,MIN(C2,5))

this will return the C2 value if the count is less than or equal to 5.

Also, in case the overcount charges are less than 5, I included the MIN(C2,5) to take the minimum of C2 value or 5.... but you can replace with just 5 if that is fixed for counts over 5....