[Solved] count up on total leave not working
- Thread starter Caitlin S
- Start date
Hello Caitlin S,
Please describe the issue you are facing in details and you can upload your workbook here.
Hello Caitlin S,
While working through it, I noticed that you didn’t update some of the cell references, and there was an incorrect formula used in certain places. I've corrected these issues for you. Your COUNTIF formula is wrong. You are using absolute reference for all the cells.
Your formula: =COUNTIF($D$9:$AH$9,$AJ$7)
In this formula, you fixed the row and column number that's why when you are dragging it it's not updating the corresponding cells. But only the column will be fixed and the row will auto-update while drawing down and right.
Correct Formula: =COUNTIF($D9:$AH9,AJ$7)
You didn't change any cell reference in summary sheet formula either:
Correct Formula:
=IFERROR(INDEX(Jan!AJ$9:AJ$33,MATCH($B10,Jan!$C$9:$C$33,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$33,MATCH($B10,Feb!$C$9:$C$33,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$33,MATCH($B10,Mar!$C$9:$C$33,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$33,MATCH($B10,Apr!$C$9:$C$33,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$33,MATCH($B10,May!$C$9:$C$33,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$33,MATCH($B10,Jun!$C$9:$C$33,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$33,MATCH($B10,Jul!$C$9:$C$33,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$33,MATCH($B10,Aug!$C$9:$C$33,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$33,MATCH($B10,Sep!$C$9:$C$33,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$33,MATCH($B10,Oct!$C$9:$C$33,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$33,MATCH($B10,Nov!$C$9:$C$33,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$33,MATCH($B10,Dec!$C$9:$C$33,0)),0)
In the future, please make sure to follow the formula steps carefully and adjust the cell references as needed to fit your specific sheet. This will help ensure everything works as intended.
Here is your updated workbook with the correct formula.
While working through it, I noticed that you didn’t update some of the cell references, and there was an incorrect formula used in certain places. I've corrected these issues for you. Your COUNTIF formula is wrong. You are using absolute reference for all the cells.
Your formula: =COUNTIF($D$9:$AH$9,$AJ$7)
In this formula, you fixed the row and column number that's why when you are dragging it it's not updating the corresponding cells. But only the column will be fixed and the row will auto-update while drawing down and right.
Correct Formula: =COUNTIF($D9:$AH9,AJ$7)
You didn't change any cell reference in summary sheet formula either:
Correct Formula:
=IFERROR(INDEX(Jan!AJ$9:AJ$33,MATCH($B10,Jan!$C$9:$C$33,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$33,MATCH($B10,Feb!$C$9:$C$33,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$33,MATCH($B10,Mar!$C$9:$C$33,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$33,MATCH($B10,Apr!$C$9:$C$33,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$33,MATCH($B10,May!$C$9:$C$33,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$33,MATCH($B10,Jun!$C$9:$C$33,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$33,MATCH($B10,Jul!$C$9:$C$33,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$33,MATCH($B10,Aug!$C$9:$C$33,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$33,MATCH($B10,Sep!$C$9:$C$33,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$33,MATCH($B10,Oct!$C$9:$C$33,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$33,MATCH($B10,Nov!$C$9:$C$33,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$33,MATCH($B10,Dec!$C$9:$C$33,0)),0)
In the future, please make sure to follow the formula steps carefully and adjust the cell references as needed to fit your specific sheet. This will help ensure everything works as intended.
Here is your updated workbook with the correct formula.
You are most welcome. Keep sharing Excel issues and helping each other to build a community.