# How can I calculate the last payment of the year to be the sum of all previous payments for that year and subtracting it from Research Costs value?

#### asifakhtar

How can I calculate the last payment of the year to be the sum of all previous payments(adding all data-amount) for that year and then subtracting the result from Research Costs value from the first row for that year and then assign the value to the last payment’s text and data-amount?

e.g if I pass 26/27 value to the function then it should get all the payments where data-payquarters contains 27/28 and then it should do sum of first 11 payments(in this case, adding all data-amount) and then subtract it from 250,254.21 And assign 20854.49 to the last payment’s text and data-amount (in this case it is the 12th payment.

e.g if I pass 27/28 value to the function then it should get all the payments where data-payquarters contains 27/28 and then it should do sum of first 5 payments(in this case) and then subtract it from 205,121.01 And assign 34,186.81 to the last payment’s text and data-amount (in this case it is the 6th payment.

HTML:
``````table class="table table-sm table-bordered">
<tr>
<th style="width:10%" class="headercell text-right">Research Costs</th>
<th style="width:10%" class="headercell text-center">Payment Schedule</th>
<th style="width:10%" class="headercell text-center">Status</th>
<th style="width:10%" class="headercell text-right">Amount</th>
<th style="width:10%" class="headercell text-right">Reprofiled Amount</th>
</tr>
<tbody id="tbPayments">
</tbody>
<tbody id="tbPayments">
<tr id="trPayment_23322651" data-year="4" data-type="payment" data-eventid="23322651">
<th>1 Apr 2026 - 31 Mar 2027</th>
<td class="text-right">250,254.21</td>
<td class="text-center" data-date="2026-04-01">01-04-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322651" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322651" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="4" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322652" data-year="4" data-type="payment" data-eventid="23322652">
<th></th>
<td></td>
<td class="text-center" data-date="2026-05-01">01-05-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322652" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322652" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="5" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322653" data-year="4" data-type="payment" data-eventid="23322653">
<th></th>
<td></td>
<td class="text-center" data-date="2026-06-01">01-06-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322653" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322653" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="6" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322654" data-year="4" data-type="payment" data-eventid="23322654">
<th></th>
<td></td>
<td class="text-center" data-date="2026-07-01">01-07-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322654" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322654" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="7" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322655" data-year="4" data-type="payment" data-eventid="23322655">
<th></th>
<td></td>
<td class="text-center" data-date="2026-08-01">01-08-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322655" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322655" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="8" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322656" data-year="4" data-type="payment" data-eventid="23322656">
<th></th>
<td></td>
<td class="text-center" data-date="2026-09-01">01-09-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322656" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322656" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="9" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322657" data-year="5" data-type="payment" data-eventid="23322657">
<th></th>
<td></td>
<td class="text-center" data-date="2026-10-01">01-10-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322657" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322657" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="10" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322658" data-year="5" data-type="payment" data-eventid="23322658">
<th></th>
<td></td>
<td class="text-center" data-date="2026-11-01">01-11-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322658" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322658" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="11" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322659" data-year="5" data-type="payment" data-eventid="23322659">
<th></th>
<td></td>
<td class="text-center" data-date="2026-12-01">01-12-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322659" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322659" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="12" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322660" data-year="5" data-type="payment" data-eventid="23322660">
<th></th>
<td></td>
<td class="text-center" data-date="2027-01-01">01-01-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322660" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322660" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="1" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322661" data-year="5" data-type="payment" data-eventid="23322661">
<th></th>
<td></td>
<td class="text-center" data-date="2027-02-01">01-02-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322661" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322661" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="2" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322662" data-year="5" data-type="payment" data-eventid="23322662">
<th></th>
<td></td>
<td class="text-center" data-date="2027-03-01">01-03-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322662" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322662" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="3" data-amount="20854.52">20,854.52</td>
</tr>

<tr id="trPayment_23322663" data-year="5" data-type="payment" data-eventid="23322663">
<th>1 Apr 2027 - 31 Mar 2028</th>
<td class="text-right">205,121.01</td>
<td class="text-center" data-date="2027-04-01">01-04-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322663" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322663" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="4" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322664" data-year="5" data-type="payment" data-eventid="23322664">
<th></th>
<td></td>
<td class="text-center" data-date="2027-05-01">01-05-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322664" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322664" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="5" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322665" data-year="5" data-type="payment" data-eventid="23322665">
<th></th>
<td></td>
<td class="text-center" data-date="2027-06-01">01-06-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322665" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322665" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="6" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322666" data-year="5" data-type="payment" data-eventid="23322666">
<th></th>
<td></td>
<td class="text-center" data-date="2027-07-01">01-07-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322666" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322666" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="7" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322667" data-year="5" data-type="payment" data-eventid="23322667">
<th></th>
<td></td>
<td class="text-center" data-date="2027-08-01">01-08-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322667" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322667" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="8" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322668" data-year="5" data-type="payment" data-eventid="23322668">
<th></th>
<td></td>
<td class="text-center" data-date="2027-09-01">01-09-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322668" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322668" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="9" data-amount="34,186.84">34,186.84</td>
</tr>
</tbody>

</table>``````

Last edited:

#### asifakhtar

SORRY Updated. There was a typo.

How can I calculate the last payment of the year to be the sum of all previous payments(adding all data-amount) for that year and then subtracting the result from Research Costs value from the first row for that year and then assign the value to the last payment’s text and data-amount?

e.g if I pass 26/27 value to the function then it should get all the payments where data-payquarters contains 26/27 and then it should do sum of first 11 payments(in this case, adding all data-amount) and then subtract it from 250,254.21 And assign 20854.49 to the last payment’s text and data-amount (in this case it is the 12th payment.

e.g if I pass 27/28 value to the function then it should get all the payments where data-payquarters contains 27/28 and then it should do sum of first 5 payments(in this case) and then subtract it from 205,121.01 And assign 34,186.81 to the last payment’s text and data-amount (in this case it is the 6th payment.

The goal is to balance the book, using the last value of the table to make up for any difference for a specific year using data-amount of "tdNewPayment_”.

I ran the code you provided to understand what you're asking, but the results were gibberish. Try it and see.
You also forgot to include the CSS. Please correct the errors and give us the CSS.

#### VBService

Check, is that what you expected?

[ on-line ]
HTML:
``````<style>
tr:has(th + td:not(:empty)) { /* tr:has(td:first-of-type:not(:empty)) */
background-color: hsl(0 0% 0% /.1);
}
</style>

<table class="table table-sm table-bordered">
<tr>
<th style="width:10%" class="headercell text-right">Research Costs</th>
<th style="width:10%" class="headercell text-center">Payment Schedule</th>
<th style="width:10%" class="headercell text-center">Status</th>
<th style="width:10%" class="headercell text-right">Amount</th>
<th style="width:10%" class="headercell text-right">Reprofiled Amount</th>
</tr>
<tbody id="tbPayments">
<tr id="trPayment_23322651" data-year="4" data-type="payment" data-eventid="23322651">
<th>1 Apr 2026 - 31 Mar 2027</th>
<td class="text-right">250,254.21</td>
<td class="text-center" data-date="2026-04-01">01-04-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322651" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322651" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="4" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322652" data-year="4" data-type="payment" data-eventid="23322652">
<th></th>
<td></td>
<td class="text-center" data-date="2026-05-01">01-05-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322652" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322652" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="5" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322653" data-year="4" data-type="payment" data-eventid="23322653">
<th></th>
<td></td>
<td class="text-center" data-date="2026-06-01">01-06-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322653" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322653" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="6" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322654" data-year="4" data-type="payment" data-eventid="23322654">
<th></th>
<td></td>
<td class="text-center" data-date="2026-07-01">01-07-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322654" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322654" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="7" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322655" data-year="4" data-type="payment" data-eventid="23322655">
<th></th>
<td></td>
<td class="text-center" data-date="2026-08-01">01-08-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322655" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322655" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="8" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322656" data-year="4" data-type="payment" data-eventid="23322656">
<th></th>
<td></td>
<td class="text-center" data-date="2026-09-01">01-09-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322656" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322656" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="9" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322657" data-year="5" data-type="payment" data-eventid="23322657">
<th></th>
<td></td>
<td class="text-center" data-date="2026-10-01">01-10-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322657" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322657" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="10" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322658" data-year="5" data-type="payment" data-eventid="23322658">
<th></th>
<td></td>
<td class="text-center" data-date="2026-11-01">01-11-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322658" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322658" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="11" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322659" data-year="5" data-type="payment" data-eventid="23322659">
<th></th>
<td></td>
<td class="text-center" data-date="2026-12-01">01-12-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322659" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322659" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="12" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322660" data-year="5" data-type="payment" data-eventid="23322660">
<th></th>
<td></td>
<td class="text-center" data-date="2027-01-01">01-01-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322660" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322660" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="1" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322661" data-year="5" data-type="payment" data-eventid="23322661">
<th></th>
<td></td>
<td class="text-center" data-date="2027-02-01">01-02-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322661" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322661" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="2" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322662" data-year="5" data-type="payment" data-eventid="23322662">
<th></th>
<td></td>
<td class="text-center" data-date="2027-03-01">01-03-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322662" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322662" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="3" data-amount="20854.52">20,854.52</td>
</tr>

<tr id="trPayment_23322663" data-year="5" data-type="payment" data-eventid="23322663">
<th>1 Apr 2027 - 31 Mar 2028</th>
<td class="text-right">205,121.01</td>
<td class="text-center" data-date="2027-04-01">01-04-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322663" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322663" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="4" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322664" data-year="5" data-type="payment" data-eventid="23322664">
<th></th>
<td></td>
<td class="text-center" data-date="2027-05-01">01-05-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322664" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322664" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="5" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322665" data-year="5" data-type="payment" data-eventid="23322665">
<th></th>
<td></td>
<td class="text-center" data-date="2027-06-01">01-06-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322665" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322665" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="6" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322666" data-year="5" data-type="payment" data-eventid="23322666">
<th></th>
<td></td>
<td class="text-center" data-date="2027-07-01">01-07-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322666" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322666" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="7" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322667" data-year="5" data-type="payment" data-eventid="23322667">
<th></th>
<td></td>
<td class="text-center" data-date="2027-08-01">01-08-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322667" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322667" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="8" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322668" data-year="5" data-type="payment" data-eventid="23322668">
<th></th>
<td></td>
<td class="text-center" data-date="2027-09-01">01-09-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322668" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322668" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="9" data-amount="34,186.84">34,186.84</td>
</tr>
</tbody>
</table>

<script>

showAllFirstPayments('26/27');
showAllFirstPayments('27/28');
}

function showAllFirstPayments(payquarter='none') {
const allRowsMatchedByPayquarter = [...document.querySelectorAll(`tr:has([data-payquarters*="\${payquarter}"])`)];
if (allRowsMatchedByPayquarter.length > 0) {
const lastRowMatchedByPayquarter = allRowsMatchedByPayquarter.pop(); // first n payments - last
const researchCostsByPayquarter = parseToAmount(allRowsMatchedByPayquarter[0].querySelector('th + td').textContent);
const sumAllFirstPayments = sumAllPreviousPayments(allRowsMatchedByPayquarter);
lastRowMatchedByPayquarter.querySelector('td[data-type="payment"]')
.textContent = amountToUSNotation((researchCostsByPayquarter - sumAllFirstPayments).toFixed(2));
} else {
console.warn(`Couldn't find matched rows by payquarter for \${payquarter} value.`);
}
}

function sumAllPreviousPayments(data) {
return [...data]
.map(element => element.querySelector('td[data-type="payment"]').dataset.amount)
.map(element => parseToAmount(element))
.reduce((total, current) => total + current);
}

function parseToAmount(value) {
try {
return parseFloat(value.replace(',', ''));
} catch {
return 0;
}
}

function amountToUSNotation(value) {
try {
//return value.toLocaleString('en-US', { style: 'decimal', minimumFractionDigits: 2, maximumFractionDigits: 2 });
return value.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
} catch {
return 0;
}
}
</script>``````

Last edited:

#### asifakhtar

Check, is that what you expected?

[ on-line ]
HTML:
``````<style>
tr:has(th + td:not(:empty)) { /* tr:has(td:first-of-type:not(:empty)) */
background-color: hsl(0 0% 0% /.1);
}
</style>

<table class="table table-sm table-bordered">
<tr>
<th style="width:10%" class="headercell text-right">Research Costs</th>
<th style="width:10%" class="headercell text-center">Payment Schedule</th>
<th style="width:10%" class="headercell text-center">Status</th>
<th style="width:10%" class="headercell text-right">Amount</th>
<th style="width:10%" class="headercell text-right">Reprofiled Amount</th>
</tr>
<tbody id="tbPayments">
<tr id="trPayment_23322651" data-year="4" data-type="payment" data-eventid="23322651">
<th>1 Apr 2026 - 31 Mar 2027</th>
<td class="text-right">250,254.21</td>
<td class="text-center" data-date="2026-04-01">01-04-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322651" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322651" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="4" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322652" data-year="4" data-type="payment" data-eventid="23322652">
<th></th>
<td></td>
<td class="text-center" data-date="2026-05-01">01-05-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322652" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322652" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="5" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322653" data-year="4" data-type="payment" data-eventid="23322653">
<th></th>
<td></td>
<td class="text-center" data-date="2026-06-01">01-06-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322653" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322653" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="6" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322654" data-year="4" data-type="payment" data-eventid="23322654">
<th></th>
<td></td>
<td class="text-center" data-date="2026-07-01">01-07-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322654" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322654" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="7" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322655" data-year="4" data-type="payment" data-eventid="23322655">
<th></th>
<td></td>
<td class="text-center" data-date="2026-08-01">01-08-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322655" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322655" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="8" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322656" data-year="4" data-type="payment" data-eventid="23322656">
<th></th>
<td></td>
<td class="text-center" data-date="2026-09-01">01-09-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322656" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322656" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="9" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322657" data-year="5" data-type="payment" data-eventid="23322657">
<th></th>
<td></td>
<td class="text-center" data-date="2026-10-01">01-10-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322657" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322657" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="10" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322658" data-year="5" data-type="payment" data-eventid="23322658">
<th></th>
<td></td>
<td class="text-center" data-date="2026-11-01">01-11-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322658" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322658" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="11" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322659" data-year="5" data-type="payment" data-eventid="23322659">
<th></th>
<td></td>
<td class="text-center" data-date="2026-12-01">01-12-2026</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322659" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322659" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="12" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322660" data-year="5" data-type="payment" data-eventid="23322660">
<th></th>
<td></td>
<td class="text-center" data-date="2027-01-01">01-01-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322660" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322660" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="1" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322661" data-year="5" data-type="payment" data-eventid="23322661">
<th></th>
<td></td>
<td class="text-center" data-date="2027-02-01">01-02-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322661" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322661" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="2" data-amount="20854.52">20,854.52</td>
</tr>
<tr id="trPayment_23322662" data-year="5" data-type="payment" data-eventid="23322662">
<th></th>
<td></td>
<td class="text-center" data-date="2027-03-01">01-03-2027</td>
<td class="text-center">Requested</td>
<td class="text-right " id="tdPayment_23322662" data-type="payment" data-amount="20,854.52">20,854.52</td>
<td class="text-right " id="tdNewPayment_23322662" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="3" data-amount="20854.52">20,854.52</td>
</tr>

<tr id="trPayment_23322663" data-year="5" data-type="payment" data-eventid="23322663">
<th>1 Apr 2027 - 31 Mar 2028</th>
<td class="text-right">205,121.01</td>
<td class="text-center" data-date="2027-04-01">01-04-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322663" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322663" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="4" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322664" data-year="5" data-type="payment" data-eventid="23322664">
<th></th>
<td></td>
<td class="text-center" data-date="2027-05-01">01-05-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322664" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322664" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="5" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322665" data-year="5" data-type="payment" data-eventid="23322665">
<th></th>
<td></td>
<td class="text-center" data-date="2027-06-01">01-06-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322665" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322665" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="6" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322666" data-year="5" data-type="payment" data-eventid="23322666">
<th></th>
<td></td>
<td class="text-center" data-date="2027-07-01">01-07-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322666" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322666" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="7" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322667" data-year="5" data-type="payment" data-eventid="23322667">
<th></th>
<td></td>
<td class="text-center" data-date="2027-08-01">01-08-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322667" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322667" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="8" data-amount="34,186.84">34,186.84</td>
</tr>
<tr id="trPayment_23322668" data-year="5" data-type="payment" data-eventid="23322668">
<th></th>
<td></td>
<td class="text-center" data-date="2027-09-01">01-09-2027</td>
<td class="text-center">Paid</td>
<td class="text-right nochange paid" id="tdPayment_23322668" data-type="payment" data-amount="34,186.84">34,186.84</td>
<td class="text-right nochange paid" id="tdNewPayment_23322668" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="9" data-amount="34,186.84">34,186.84</td>
</tr>
</tbody>
</table>

<script>

showAllFirstPayments('26/27');
showAllFirstPayments('27/28');
}

function showAllFirstPayments(payquarter='none') {
const allRowsMatchedByPayquarter = [...document.querySelectorAll(`tr:has([data-payquarters*="\${payquarter}"])`)];
if (allRowsMatchedByPayquarter.length > 0) {
const lastRowMatchedByPayquarter = allRowsMatchedByPayquarter.pop(); // first n payments - last
const researchCostsByPayquarter = parseToAmount(allRowsMatchedByPayquarter[0].querySelector('th + td').textContent);
const sumAllFirstPayments = sumAllPreviousPayments(allRowsMatchedByPayquarter);
lastRowMatchedByPayquarter.querySelector('td[data-type="payment"]')
.textContent = amountToUSNotation((researchCostsByPayquarter - sumAllFirstPayments).toFixed(2));
} else {
console.warn(`Couldn't find matched rows by payquarter for \${payquarter} value.`);
}
}

function sumAllPreviousPayments(data) {
return [...data]
.map(element => element.querySelector('td[data-type="payment"]').dataset.amount)
.map(element => parseToAmount(element))
.reduce((total, current) => total + current);
}

function parseToAmount(value) {
try {
return parseFloat(value.replace(',', ''));
} catch {
return 0;
}
}

function amountToUSNotation(value) {
try {
//return value.toLocaleString('en-US', { style: 'decimal', minimumFractionDigits: 2, maximumFractionDigits: 2 });
return value.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ',');
} catch {
return 0;
}
}
</script>``````
Hey txs for the soultion but I get Uncaught DOMException: Document.querySelectorAll: 'tr:has([data-payquarters*="26/27"])' is not a valid selector error when I run it.

#### VBService

Document.querySelectorAll: 'tr:has([data-payquarters*="26/27"])'
The code written in this way is invalid.
1. document - all lower case
2. document.querySelectorAll: - no colon at the end

JavaScript:
``document.querySelectorAll('tr:has([data-payquarters*="26/27"])')``

#### asifakhtar

The code written in this way is invalid.
1. document - all lower case
2. document.querySelectorAll: - no colon at the end

JavaScript:
``document.querySelectorAll('tr:has([data-payquarters*="26/27"])')``
Same error: Uncaught DOMException: Document.querySelectorAll: 'tr:has([data-payquarters*="26/27"])' is not a valid selector

#### VBService

I think the problem is with :has Check compatibility with your browser.
If your browser not support :has, that mean we need to find different way for this part of code.

JavaScript:
``const allRowsMatchedByPayquarter = [...document.querySelectorAll(`tr:has([data-payquarters*="\${payquarter}"])`)];``

## Ask a Question

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.