Incorrect subtotals

[sirrahrc]

Hi. I'm an occasional excel user and am using it mainly for domestic purposes.


I have just created a workbook and am using subtotals for each year. However, some of those subtotals are wrong and I can't work out why.

For example, the total of a column relating to 2017 adds up correctly; the formula used is =SUBTOTAL(9,R4:R8). The amounts for 2018 use the formula =SUBTOTAL(9,R10:R17). Similarly, 2019 is correct with =SUBTOTAL(9,R19:R26). However, 2020 shows a total of 0 with the formula =SUBTOTAL(9,R28:R35) whereas the figures included in the range come to about 15000. 2021 (although incomplete) is also showing the wring total. I keep finding seemingly random incorrect subtotals.

I can't see anything wrong with the formulas but I'm sure there must be. I am attaching some screenshots of the column and of the formula for each of the 4 subtotals and would appreciate if someone can show me where I have gone wrong.

Thanks

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[sirrahrc]

Thank you.

I have just removed personal details from the workbook and it is now attached.
The purpose is to analyse the income and expenses from two properties and then to allocate any profit or loss to four people who own varying percentage shares.

[Glenn Kennedy]

So, where's the problem??

Do you have calculation options set to manual??

[Glenn Kennedy]

Scrub last comment. Found them now!!

[sirrahrc]

No. it's just that some of the subtotals are clearly wrong. For example R36 and R47.

Thanks

[Glenn Kennedy]

Circular references. Look, for example, at the formula in H33:


=SUM(B32:H34) It should be =SUM(B32:G34)

[Glenn Kennedy]

I don't see the problem in R47...

[sirrahrc]

Sorry, R47 is OK but R36 shows a zero subtotal.

Thanks for spotting H33; I have corrected that.

[AliGW]

Drag copy H28 down to H35 to get rid of all the errors to which Glenn referred, then R36 will be fine.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[AliGW]

Thank you for the kind rep comment, however you selected the disapprove button, so I have lost points and got a negative rep mark.

It was the thought that counted, though. Glad you are sorted.

[sirrahrc]

So sorry. Is there a way I can correct that?

[AliGW]

It really doesn't matter - just wanted to let you know so that you are aware that there are two types of reputation. The kind comment was enough.

[sirrahrc]

Thanks again for your help.