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.