Column sum works in "Detail" but not "Footer"

[Doodlebug2000]

I created a report that presents data from a crosstab query. There are 3 groups and 5 categories and there's a number in each "cell". I want to make totals for each of these categories.

My control source for this total field is =sum([Category1]). I get the correct total in Detail, though it repeats 3 times because I have 3 groups. But when I move to the Footer, I get a #error.


How can I resolve this?

thanks

[RuralGuy]

Create invisible controls on your Report that calculate the Sum for you and then just reference the Sum in the Footer.