Forum Discussion

Cambosity100's avatar
Cambosity100
Brass Contributor
Mar 23, 2021
Solved

Available Kms per week

Hi all. I am a coordinator who works with people with disability. One of the sticky problems is tracking available Kms for these participants each week. Commonly these participants are allocated a bu...
excel
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Mar 23, 2021

    Cambosity100 

    You did not adjust the input ranges in the formula.

    The input columns are columns G:H, right? Therefore the formula in G3 should be...

    =B7-SUMPRODUCT((WEEKNUM(--(G2:G100))=WEEKNUM(B2))*(H2:H100))

    You may adjust the input ranges G2:G100 and H2:H100 as per your requirement.

    I have inserted one new record and the formula returned the balance kms correctly. Please refer to the attached.

     

     

  • Cambosity100's avatar
    Cambosity100
    Brass Contributor
    Mar 23, 2021

    Subodh_Tiwari_sktneer 

    Thankyou for looking. Unfortunately the formula only returns the same figure for Kms per week and is not affected by new input.... I have attached a reformatted spreadsheet with your formula inserted to hopefully highlight the issue  🙂

    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      Mar 23, 2021

      Cambosity100 

      You did not adjust the input ranges in the formula.

      The input columns are columns G:H, right? Therefore the formula in G3 should be...

      =B7-SUMPRODUCT((WEEKNUM(--(G2:G100))=WEEKNUM(B2))*(H2:H100))

      You may adjust the input ranges G2:G100 and H2:H100 as per your requirement.

      I have inserted one new record and the formula returned the balance kms correctly. Please refer to the attached.

       

       

      • Cambosity100's avatar
        Cambosity100
        Brass Contributor
        Apr 01, 2021
        Sorry for being so slow in thanking you .I have been away from my laptop. I appreciate your time very much 🙂