Excel function help, please

Mags_cat

I've been tweaking last year's budget/spending diary to take account of what worked and what didn't.

This year's has monthly pages which record income and expenditure - dropdown categories which subtotal on the month's page. Those then copy over onto an annual summary sheet.

Trouble is, one of my subtotalling functions is refusing to work. I have a list of dropdowns and I use the following formula to get a subtotal :

=SUMIF($C$4:$C$119,"=Dropdown name",$D$4:$D$119)

So - if there are five entries marked "Groceries" within the limits of that function on the sheet it should count them all up and transfer the subtotal to the annual summary page.

But the blinkin' "Groceries" is refusing to total. Every other dropdown category on the page works...but not that one. When I set it up, I copied the formula and just changed the name of the dropdown category to suit. I've checked it up, down and sideways and I simply can't make that particular category add up. It's driving me batty...(battier!).

Can anyone help?

garrydh

If you're 100% sure that the formula's correct for other categories then the only thing I can think of is the spelling of "Groceries". I'd suggest making sure that the spelling is consistent and there's no additional spaces anywhere which might not be visible to the naked eye, for example " Groceries" or "Groceries ".

Mags_cat

Thanks, Gary - that's exactly what I have been checking - and it all looks the same to me, on the dropdown sheet, on the category and on the subtotal...still won't add up though. :mad:

Ho hum - may end up having to manually total that one - not the end of the world but I wish it'd picked a category I use less! :rotfl:

Bikertov

If you hit F2 on the cell with the formula in it, is it highlighting the correct cell ranges ?

Also, try highlighting all the cells - do you see them total up correctly on the bottom of the spreadsheet ?

What if you created a new simple sum of the whole range - does that correctly total ?

It may be the individual cell properties are 'general' or 'text', and not numbers ...

Mags_cat

Found it!

There was another interconnected (and hidden) sheet that had an error on it.

That'll teach me to try and be complex....

Thanks for your suggestions, chaps - certainly helped me work out what the problem was, if only by making me critically examine things :T