There are certain situations where SUMIFS returns values I do not expect when there are multiple criteria. Can you help me understand what is going on here and how I can get the expected result?
This screenshot shows the data I am using, the values I get, and the values I expected to get:
The data is in a table called "Table1". Here are the four formulas I am using:
=SUM(SUMIFS(Table1[Value],Table1[Letter],{"A","B"},Table1[Greek],{"Alpha"}))
=SUM(SUMIFS(Table1[Value],Table1[Letter],{"A","B"},Table1[Greek],{"Kappa"}))
=SUM(SUMIFS(Table1[Value],Table1[Letter],{"A","B"},Table1[Greek],{"Alpha","Beta","Kappa"}))
=SUM(SUMIFS(Table1[Value],Table1[Letter],{"A","B"},Table1[Greek],{"Alpha","Kappa"}))
The first two statements work as expected, but when I add multiple options in the second "criteria" then the numbers don't add as I expect them to.
In the third statement, for example, I would expect that it adds up the first four rows (everything with (Letter A or B) AND (Greek of Alpha, Beta, or Kappa)) which would total 70. Instead it comes back with 10.
Using Excel 2010 on Windows 7.
When using two arrays in a sumifs, one must be vertical while the other horizontal:
=SUM(SUMIFS(Table1[Value],Table1[Letter],{"A","B"},Table1[Greek],{"Alpha";"Beta";"Kappa"}))
The ,
denotes the horizontal, while the ;
denotes a vertical array.
So since there is no depth or 3rd dimension to a range of cells the most one formula can have is two arrays in the criteria.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments