SUMIFS with Multiple Criteria with Multiple Values

ThatFella

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:

enter image description here

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.

Scott Craner

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.

edited at
0

Comments

0 comments
Login to comment

Related

TOP Ranking

  1. 1

    Failed to listen on localhost:8000 (reason: Cannot assign requested address)

  2. 2

    Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

  3. 3

    How to import an asset in swift using Bundle.main.path() in a react-native native module

  4. 4

    pump.io port in URL

  5. 5

    Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

  6. 6

    BigQuery - concatenate ignoring NULL

  7. 7

    ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

  8. 8

    ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

  9. 9

    Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

  10. 10

    How to remove the extra space from right in a webview?

  11. 11

    java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

  12. 12

    Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

  13. 13

    flutter: dropdown item programmatically unselect problem

  14. 14

    How to use merge windows unallocated space into Ubuntu using GParted?

  15. 15

    Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

  16. 16

    Nuget add packages gives access denied errors

  17. 17

    Svchost high CPU from Microsoft.BingWeather app errors

  18. 18

    Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

  19. 19

    12.04.3--- Dconf Editor won't show com>canonical>unity option

  20. 20

    Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

  21. 21

    maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

HotTag

Archive