Hi team,
Could someone please assist to review my file.
I'm working with a resource file to come up with a single rate for Multiple payscale to charge client.
Thanks for your help.
Kind regards
John
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Sumifs single and Mutiple criteria
0
Discussion
Hello siehartley2001,
I won't have a chance to work on this until some time next week. I hope to have a solution by the end of he week.
I won't have a chance to work on this until some time next week. I hope to have a solution by the end of he week.
WillieD24 (rep: 738) Jun 6, '26 at 12:41 pm
Hi siehartley2001,
The reason your formula doesn't work is because you are asking it to return the number of days a staff member works in multiple pay scales. But each staff member (as per your "data" worksheet) only ever works in one pay scale, not multiple pay scales.
For example - "Rate 1": you need to separately total the number of days a staff member works in pay scale "DRD 1", the number days in pay scale "DRD 2", and the number of days in pay scale "Grade 1" then SUM those totals. In the file I have attached in my answer, on the "Solution 1" worksheet you will see the formula under "Rate 1" has three parts, "Rate 2" and "Rate 3" each only have one part, and "Rate 4" has four parts.
If this solves things for you please mark my answer as Selected.
Cheers :-)
The reason your formula doesn't work is because you are asking it to return the number of days a staff member works in multiple pay scales. But each staff member (as per your "data" worksheet) only ever works in one pay scale, not multiple pay scales.
For example - "Rate 1": you need to separately total the number of days a staff member works in pay scale "DRD 1", the number days in pay scale "DRD 2", and the number of days in pay scale "Grade 1" then SUM those totals. In the file I have attached in my answer, on the "Solution 1" worksheet you will see the formula under "Rate 1" has three parts, "Rate 2" and "Rate 3" each only have one part, and "Rate 4" has four parts.
If this solves things for you please mark my answer as Selected.
Cheers :-)
WillieD24 (rep: 738) Jun 10, '26 at 4:24 pm
Answers
0
Hello siehartley2001,
My plans for today changed so I decided to take a look at your file.
I decided to build a formula where it adds the days a staff member works in each pay scale. Based on your "data" sheet each staff member works in only one pay scale.
For example: Cell D9 - the total number of days that staffer 30169566 works for pay scales "DRD 1" plus "DRD 2" plus "Grade 1". The formulas in Column D are the totals by staffer for pay scales "DRD 1" plus "DRD 2" plus "Grade 1". The totals in "Column E" are for pay scale "Grade 2" only; "Column F" are for pay scale "Grade 3" only; "Column G" are for pay scales "Interim" and "Grade 4" and "Grade 5" and "Grade 6".
When I initially totalled the days I got 2,899. I then realized that "Rate 4" also had "Grade 3". After removing "Grade 3" from "Rate 4" the total matched the total on the "data" sheet.
Your "data" sheet has some rough work at the bottom. Your "Help" sheet also has my rough work on it. The "Solution 1" sheet is your table with the new formulas.
I hope this is what you were hoping to achieve. If this solves things for you please mark my answer as selected.
Cheers :-)