Why is SUMIF function not working when data is formatted as a table?

December 7, 2013 8:54 AM

I am developing a sales lead sheet. I have on the B column a drop down list for the sales status (complete, no sale, and follow up). On the A column I have a dollar amount for the sales lead. Originally, I used the SUMIF function to add only the "follow up" figures referenced from Column B to track the dollar amount of outstanding leads. I then thought to convert my sheet into a table to make sorting easier, but now the SUMIF function is returning a value of zero. What did I miss? Thanks much!
posted by FireStyle to Computers & Internet (2 answers total)

If you're using a defined table, you'll want to use table references instead of range references (which has the side benefit of making your formula way more readable). The formula would look something like this:

=SUMIF(Table1[B],3,Table1[A])

If you re-write the formula, selecting the ranges as usual, Excel will write the references this way.
posted by paper chromatographologist at 9:07 AM on December 7, 2013

My first thought is that when you made the table, Excel decided column B was something other than numbers.

If you select cells in column B, do you see a sum or just a count in the status bar? You could also try putting wildcards around your criteria - "*criteria*"

Good luck. I have anger issues wirh Excel suddenly not doing something it was previously doing just fine.
posted by Lesser Shrew at 9:10 AM on December 7, 2013