Adding together the nos in 1 cell dependant on what is anoth

[Coventry Deb]

I have a large amount data split by numerous Council departments of a business rates relief. If the business rates system code ends in a 5 its discretionary rate relief, and if a 6 its top up relief. The difference between the 2 being how much is funded by the Council and how much is funded by the government.

Each department has an individual total so some cells are blank, the end of the data being say D1197 with a grand total of both types in cell D1198, covering a mix of the 2 reliefs and I was hoping to find a way (with someone out there's help) a way of having 2 further totals at the below the grand total, one for 5's and one for 6's. So if its a 5 in (say) cell A1, add the figure in cell D1 (say £100.36) to a running total in cell D1200 and keep going down in a loop until you get to the bottom, coping with the blank cells in between. If its a 6 in A1, add all the amount in the 3rd column across in cell D1 downwards in cell D1201.

Thanks :wink:

 

[Aladin Akyurek]

Re: Adding together the nos in 1 cell dependant on what is a

I have a large amount data split by numerous Council departments of a business rates relief. If the business rates system code ends in a 5 its discretionary rate relief, and if a 6 its top up relief. The difference between the 2 being how much is funded by the Council and how much is funded by the government.

Each department has an individual total so some cells are blank, the end of the data being say D1197 with a grand total of both types in cell D1198, covering a mix of the 2 reliefs and I was hoping to find a way (with someone out there's help) a way of having 2 further totals at the below the grand total, one for 5's and one for 6's. So if its a 5 in (say) cell A1, add the figure in cell D1 (say £100.36) to a running total in cell D1200 and keep going down in a loop until you get to the bottom, coping with the blank cells in between. If its a 6 in A1, add all the amount in the 3rd column across in cell D1 downwards in cell D1201.

Thanks :wink:

Care to post just a 5-row sample along with the expected results?

 

[Coventry Deb]

As requested

Committee Date Organisation Adj Key Amount

Sports 01/04/2005 Rugby Club CR5 5 (944.13)
Sports 01/04/2005 Sports Trust A BR6 6 (54,311.30)
Sports 01/04/2005 Sports Trust B BR6 6 (11,731.60)
Sports 01/04/2005 Scout Group CR5 5 (1,076.10)

Sports is just one of the departments to be recharged

CR5 etc is the adjustment code entered on the Business Rates System. I used a =right(1) to get the trigger code. This then calculates how much each individual organisation costs the council for the relief. If its a 5 the government pays 75% of the amount, if a 6 the government pays 25%. But I need a total for all the 5, and another for all the 6's. In the past I've added them up manually which takes ages.

5 or 6 is the key which triggers how much is reclaimed from gov.

Thanks

 

[Aladin Akyurek]

Re: As requested

Committee Date Organisation Adj Key Amount

Sports 01/04/2005 Rugby Club CR5 5 (944.13)
Sports 01/04/2005 Sports Trust A BR6 6 (54,311.30)
Sports 01/04/2005 Sports Trust B BR6 6 (11,731.60)
Sports 01/04/2005 Scout Group CR5 5 (1,076.10)

Sports is just one of the departments to be recharged

CR5 etc is the adjustment code entered on the Business Rates System. I used a =right(1) to get the trigger code. This then calculates how much each individual organisation costs the council for the relief. If its a 5 the government pays 75% of the amount, if a 6 the government pays 25%. But I need a total for all the 5, and another for all the 6's. In the past I've added them up manually which takes ages.

5 or 6 is the key which triggers how much is reclaimed from gov.

Thanks

Do you mean...

=SUMIF(KeyRange,Key,AmountRange)

where Key is a separate cell housing a key value of interest like 5?

BTW, I expect the RIGHT formula to be something like:

=RIGHT(Adj)+0