Sheet1 : Salary Average Calculation
| Date | Employee Name1 | Employee Name2 | Employee Name3 | Employee Name4 | Employee Name5 | Employee Name6 |
|---|---|---|---|---|---|---|
| 21-01-2025 | ||||||
| 22-01-2025 | ||||||
| 23-01-2025 | ||||||
| 24-01-2025 | ||||||
| 25-01-2025 | ||||||
| 26-01-2025 | ||||||
| 27-01-2025 | ||||||
| 28-01-2025 | ||||||
| 29-01-2025 |
Sheet1 : Salary Average Calculation
| Date | Employee Name1 | Employee Name2 | Employee Name3 | Employee Name4 | Employee Name5 | Employee Name6 |
|---|---|---|---|---|---|---|
| 21-01-2025 | ||||||
| 22-01-2025 | ||||||
| 23-01-2025 | ||||||
| 24-01-2025 | ||||||
| 25-01-2025 | ||||||
| 26-01-2025 | ||||||
| 27-01-2025 | ||||||
| 28-01-2025 | ||||||
| 29-01-2025 |
sheet 2 : Salary History
| Employee | Date of Revision | Effective until | Present | Increment | Increment % | New Salary |
|---|---|---|---|---|---|---|
| Employee Name1 | 03-11-2015 | 31-12-2019 | 15,000 | - | 0% | 15,000 |
| Employee Name1 | 01-01-2020 | 30-06-2021 | 15,000 | 5,000 | 33% | 20,000 |
| Employee Name1 | 01-07-2021 | 31-03-2022 | 20,000 | 5,000 | 25% | 25,000 |
| Employee Name1 | 01-04-2022 | 31-10-2023 | 25,000 | 5,000 | 20% | 30,000 |
| Employee Name1 | 01-11-2023 | 01-01-2100 | 30,000 | 20,000 | 67% | 50,000 |
| Employee Name2 | 08-11-2023 | 30-11-2024 | 40,000 | - | 0% | 40,000 |
| Employee Name2 | 01-12-2024 | 01-01-2100 | 40,000 | 5,000 | 13% | 45,000 |
| Employee Name3 | 08-11-2023 | 30-11-2024 | 30,000 | - | 0% | 30,000 |
| Employee Name3 | 01-12-2024 | 01-01-2100 | 30,000 | 10,000 | 33% | 40,000 |
| Employee Name5 | 02-12-2015 | 31-03-2017 | 9,500 | - | 0% | 9,500 |
I have two excel sheets namely Sheet1#'Salary Average Calculation' & Sheet2#'Salary History' (attached) I want to first match the names and then match each date in 'sheet Salary Average Calculation' - Column 'A' to see where it's falling in between or equal to column B and Column C in sheet 'Salary History' and then I want to return 'new salary' value against each row in Column B in Sheet1#'Salary Average Calculation' ( This is to calculate salary average for a particular employee for a particular duration). What will be the formula for this ? Could someone please help ?
I am new in this . Please help
Share Improve this question edited Mar 12 at 13:09 Foxfire And Burns And Burns 12k2 gold badges19 silver badges40 bronze badges asked Mar 12 at 11:27 HR- SpecializedHR- Specialized 11 silver badge2 bronze badges1 Answer
Reset to default 1Not sure if I understood properly, but looks like you want to retrieve the value New Salary for each employee in a specifid date. This specific date must be in the interval delimited by Date of Revision and Effective until. Your provided inputs from Sheet1 are all dates from 2025 so the output is the same for all of them I've added dates from different years to check if this is what you need:
I've done everything on same page but the formula will work perfectly even in different sheets.
The formula in cell B2 is:
=SUMIFS($P$2:$P$11;$J$2:$J$11;B$1;$K$2:$K$11;"<="&$A2;$L$2:$L$11;">="&$A2)
Then drag down and to right and everything should work. Please, note the formula uses absolute references but it uses also mixed references. This is important or the formula won't work