All geek questions in one place

Why braces ({}) don't work in SUMIFS in Excel?

I am trying to do this in Excel 2010:

=SUMIFS(Main!I:I,Main!J:J,"A",Main!K:K,{"OptionA","OptionB"})

I want to summarize everything Main! I: I where is this true:

  • Main!J:J - "A"
  • Main!K:K is either "OptionA" or "OptionB"

I saw curly braces on other sites, they should be similar to OR, but it does not work, I just get OptionA.

This is done in order to avoid the SUM of several SUMIFS .

Thanks!

+4
source share
2 answers

Try using the formula in the formula = sum (), for example ...

 =SUM(SUMIFS(Main!I:I,Main!J:J,"A",Main!K:K,{"OptionA","OptionB"}))

Then it will take more than just the first criteria in your braces.

+5
source

Some functions of the worksheet are simply not designed to accept array parameters, but some of them. Those that do not accept the first element of the array as their only argument. I prefer to use SUMPRODUCT when I have difficult situations of AND and OR.

 =SUMPRODUCT((J4:J14="A")*((K4:K14="OptionA")+(K4:K14="OptionB"))*(I4:I14))

Think of an asterisk as AND and plus as OR. I am pretty liberal with parentheses, but it helps me understand (for me). For each comparison, TRUE or FALSE is returned. Then the resulting arrays are multiplied or added together. TRUTH is similar to 1, and FALSE is 0.

0
source

Source: https://habr.com/ru/post/1487923/

More articles:


All Articles