Forum Discussion

Adam08780's avatar
Adam08780
Copper Contributor
Apr 07, 2024
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 f...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 07, 2024

    Adam08780 

    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.

HansVogelaar's avatar
HansVogelaar
MVP
Apr 07, 2024

Adam08780 

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.

  • Adam08780's avatar
    Adam08780
    Copper Contributor
    Apr 07, 2024
    Excellent all working
    Thank you very much

Resources