How do I SUM cells containing text and numbers within a range.

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

Extracting the numeric value from VPJ0020 can be done as follows

Please Login or Register  to view this content.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi Pepe
Thank you for your help.

I have now solved the problem. My formula:
=IF([Payment type]<>"V",SUMPRODUCT(([Array]=[Client code])/1),SUMPRODUCT(--(LEFT([Array],LEN([Client code]))=[Client code])))

Best wishes
Mike