SUMIFS giving incorrect results

Hi all! I'm having issues with a SUMIFS formula, and perhaps I'm not even using the right formula. What I want is to pull the SUM of Monetary column for a specific year (or years) and by phase. In some instances I want to add accounting period.


Column information:
H= Fiscal Year
I=Accounting Period
K=Phase
Y=Monetary

The formula I'm using is:
=SUMIFS(Y:Y,H:H,"2010",K:K,"9101")

Which should be giving me the sum of everything in 2010 phase 9101. Right? The value my formula gives is $306,250 when it should be $16,262.

So I'm obviously pulling more data than I should. Even if I change my formula to
=SUMIFS(Y2:Y1167,H2:H1167,"2010",K2:K1167,"9101") to ensure the ranges are all the same, I get the same result.


I've tried troubleshooting the Fiscal Year column to formatting it as TEXT version a date. But that doesn't work either.

Any help or ideas would be greatly appreciate.

I think this will be difficult to debug without seeing the actual data. You are correct that this should only sum those records where H is 2010 and K is 9101. It appears that it is finding more records with those identifiers than you think there are. If I were debugging this, I would probably select the main database -> Autofilter, then filter the database for H is 2010 and K is 9101. Then I can look to see if I was correctly identifying the records that should be included or see if there are more records that fit the two criteria.

If you are unfamiliar with using autofilter to filter a list/database: https://www.wikihow.com/Use-AutoFilter-in-MS-Excel

From this starting point, it is probably easier to think of "which accounts to include in the SUMIFS()" rather than think of "which accounts to exclude". My first thought is something like =SUMIFS(...,E:E,"5011110")+SUMIFS(...,E:E,"5011120")+... where you have a SUMIFS() function for each account number and add up the result.

My older version doesn't have it, but the AGGREGATE() function in conjunction with the autofilter might be useful here: https://support.microsoft.com/en-us/...rs=en-us&ad=us One of the options that can be set in the AGGREGATE() function is to ignore hidden rows. Something like =AGGREGATE(9,5,F:F) will sum up column F. Then when you use the filter to hide rows you don't want included, AGGREGATE() should ignore the hidden rows.

Since this feels like a database summary kind of thing, a pivot table or maybe power pivot (Get and Transform) might be better suited to this kind of conditional summation.