Available Kms per week

[Cambosity100]

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 budget for travel for the extent of their service agreement and that is divided by the amount of weeks available in that agreement. But travel is a very inconsistent variable. I have attached a spread sheet that has sample data with the piece I can't work out highlighted. We have support workers daily calling in to ask if travel is available this week and it has been a constant struggle to give an accurate answer. Any help is appreciated. The attachment has some simple formulas within it. All data is fictional.

[#2]

@Cambosity100 

Try this and see if this returns the desired output.

In B3

=E8-SUMPRODUCT((WEEKNUM(--(B7:B100))=WEEKNUM(B2))*(C7:C100))

[#3]

@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  :)

[#4 · Accepted solution]

@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.

[#5]

Sorry for being so slow in thanking you .I have been away from my laptop. I appreciate your time very much :)

[#6]

You're welcome @Cambosity100!
No worries, that's completely fine.