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

#### Coventry Deb

##### New Member
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:

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Re: Adding together the nos in 1 cell dependant on what is a

Coventry Deb said:
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?

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

Re: As requested

Coventry Deb said:
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:

Replies
1
Views
305
Replies
3
Views
2K
Replies
2
Views
1K
Replies
5
Views
511
Replies
4
Views
361

1,203,241
Messages
6,054,338
Members
444,717
Latest member
melindanegron

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back