Hi everyone
I have an array containing work done per client referenced by date and time (see attachment).
Below the array, I have columns containing the related client details, e.g.
A=Client name
B=Client code (this is linked to the array codes)
C=Hourly rate
D=Units (calculated from the array)
E=Hours worked (calculated)
F=Amount due (calculated)
My problem is that I am now starting to have clients with a variable hourly cost, so column "C" (hourly rate) is no longer a constant.
These variable rate clients are recognized by a client code beginning with "V". The hourly rate for these clients is found in the array, e.g.
VPJ0020 = Client code "VPJ" with a rate for this period of NT$20 per 15 mins
VPJ0030 = Client code "VPJ" (the same client) with a rate for this period of NT$30 per 15 mins
The client code for this one is "VPJ", and the amount due should be 20+30=50
My question:
How do I calculate the amount due from these 'variable rate' clients (highlighted in blue)?
My thoughts:
I need to locate the cells containing the 3 character variable client prefix (e.g. "VPJ") and then SUM the remaining 4 digits to find the working values.
I've tried using LEFT, MID, ISNUMBER, and SUBSTITUTE to try to extract the numeric value when SUMming the range, but I couldn't get it to work.
It was a really interesting problem until I ran out of options!
Any help would be appreciated.
Thank you
Bookmarks