please help create a formula

[Rhon Alvarez]

Hi Team,

I have this dilemma on my worksheet wherein I need to identify the equivalent charges for a transfer amount based on the table on the left.
here are some of the requirements:

1. formulas should reflect on D column
2. D2 & D3 formula to reflect the actual charges based on transfer amount
3. D4 charges will be based on the total of transferred amount irregardless of how many times a certain number transacts
   ex: c4 & c5 (5000 + 3000) total of 8000 charge should only be 100
4. D5 since same number with D4, should already be blank
5. D6 same with D4 except that it exceeds the 10000 transferred amount therefore, additional 10 for every 1000 or a fraction thereof.
   ex c6 + c7 + c8 (5000 + 5000 + 5000) = 15000
   10000 = 100 charge + 5000/1000 = 5 X 10 or 50 total of 150 charge
6. D7 & D8 should already be blanks

Thanks in advance.

Rhon

 

[Excel Wizard]

Please try at D2

=(COUNTIFS(C$2:C2,C2)=1)*(LOOKUP(SUMIFS($B$2:$B$8,$C$2:$C$8,C2),$F$2:$H$10)+FLOOR(MAX(0,SUMIFS($B$2:$B$8,$C$2:$C$8,C2)-$G$10)/100,10))