Forum Discussion
Solved
Formulas Not Working
1st formula trying to fix why its not showing contract for last month at the bottom
2nd formula trying to show most common duty for that tech by month
3rd formula trying to count number of duties for that month
The 3rd formula I tried I don't understand why it's returning #Value! when the values are the same
Please see file attached
Thank you for your time
1) The formula in F2 should be
=INDEX(Contracts!$D$2:$AO$20,MATCH(Calcs_Logs!$D3,Contracts!$C$2:$C$20,0),MATCH(Calcs_Logs!$E3,Contracts!$D$1:$AO$1,0))
or slightly better:
=LET(v,INDEX(Contracts!$D$2:$AO$20,MATCH(Calcs_Logs!$D3,Contracts!$C$2:$C$20,0),MATCH(Calcs_Logs!$E3,Contracts!$D$1:$AO$1,0)),IF(v="","",v))
2) The formula for the most frequent duty is complicated, see the attached workbook.
3) The count formula cannot use COUNTIFS since you;re referring to ranges with different sizes. Use SUM instead.
2 Replies
1) The formula in F2 should be
=INDEX(Contracts!$D$2:$AO$20,MATCH(Calcs_Logs!$D3,Contracts!$C$2:$C$20,0),MATCH(Calcs_Logs!$E3,Contracts!$D$1:$AO$1,0))
or slightly better:
=LET(v,INDEX(Contracts!$D$2:$AO$20,MATCH(Calcs_Logs!$D3,Contracts!$C$2:$C$20,0),MATCH(Calcs_Logs!$E3,Contracts!$D$1:$AO$1,0)),IF(v="","",v))
2) The formula for the most frequent duty is complicated, see the attached workbook.
3) The count formula cannot use COUNTIFS since you;re referring to ranges with different sizes. Use SUM instead.
- Excellent all working
Thank you very much
