Totals do not add up properly

Hello. I have written a spreadsheet with three sheets (see attached) You can see in sheet 3 ('totals') some formulas are displaying as text, and column AC has a 6 in which is not reflected in the total in AC12. If one total is not adding up properly it makes you wonder about all the others too!

I should mention I get a circular error message when I open the file, but when I ran the formula check tab thingy it said it was OK: I think that error just appears because Excel expects my formula to select cells next to the ones I selected but I don't want those cells. So I am ignoring that.

Thanks!

Those cells are formatted as Text. The easiest way to fix it is to select the column (one column at a time) and do "Text to Columns" (Data tab) and then click "Finish"
Does that work for you?

Hi,

You have at least one circular reference in Y2 on Sheet 2. This formula (

Formula:

=SUM(Staff1:Staff2!Y2)

) is of course referencing itself hence the circular argument. All similar formulae are of course the same.

As a general point I suspect you really need to reconsider your whole layout so that you have a 'normalised' 2 dimensional table that could be used by a Pivot table, or indeed any other functions.

So consider a single sheet database where you have columns for

Date
Book types (currently column A) (you could set up a drop down data validation list)
Staff Type - i.e. populated with 1 or 2 (from data validation list)
FT Type i.e. populated with "FT", "Local FT", "NFT" and presumaby "bibs" (from data validation list
Numbers

Without knowing your process and overall intention it's a little difficult to comment further