Sumifs and data validation in Excel 2010

**judasdac** · 05-21-2011, 05:34 PM

Hi All,
I'm a bit perplexed right now. My managers are scared of pivot tables so I'm just trying to give them a simple sheet with a data validation cell they can select from that will mimic the results of a pivot table by using a sumifs formula.

The dropdown data validation is in cell B10. The options for the data validation is a list of account codes that are four digits all of which are numbers. For example, 0015, 6008, 0295, etc.

The sumifs formula is

Please Login or Register  to view this content.

In some situations, the sumifs formula works perfectly and for others it does not. I can't quite figure out what the problem is. Any ideas? I've been struggling with this since yesterday am at a loss.

Cheers and thanks in advance!

I've attached the file.

**teylyn** · 05-21-2011, 07:42 PM

Hello,

can you detail a situation where the formula does not work? Not all the values in the data validation list are present in the Expense Data Table. For example 0922 is not. So if that value is selected, the Sumifs formula will return a zero.

**jaslake** · 05-21-2011, 08:29 PM

Hi judasdac
Don't know if this is contributing to your issue but I'd make my named ranges dynamic. For example, FundID is defined as "=References!$H$2:$H$90" but it extends to "$H$92". This may be entirely appropriate for your issue...don't know. If it's not appropriate, see this http://www.ozgrid.com/Excel/DynamicRanges.htm. Seems to be an anomaly.