Summation with “OR” criterion in Excel

Summing with an “OR” criterion in Excel can be a little more complicated than you might expect. In this article you will find some simple but useful examples.

1. Let’s start simple. Let’s say you want to sum the cells that meet the following criteria: contain the words “Google” or “Facebook” (one column with criteria). The following formula can easily do this:

   =SUMIF(A1:A8,"Google",B1:B8)+SUMIF(A1:A8,"Facebook",B1:B8)

   =СУММЕСЛИ(A1:A8;"Google";B1:B8)+СУММЕСЛИ(A1:A8;"Facebook";B1:B8)

2. If we have 2 columns with criteria, and we also want to sum the cells in the rows that contain “Google” or “Stanford” (two ranges of criteria), then we can’t just use the function SUMIF (SUMIF) twice (see figure below), as in the previous example. Cells with values ​​in rows 1 and 7 are summed twice when they should only be counted once. 10 is the correct result!

   =SUMIF(A1:A8,"Google",C1:C8)+SUMIF(B1:B8,"Facebook",C1:C8)

   =СУММЕСЛИ(A1:A8;"Google";C1:C8)+СУММЕСЛИ(B1:B8;"Facebook";C1:C8)

   

3. We need an array formula. We use the function IF (IF) to check if the criteria “Google” or “Stanford” match in a string:

   =IF((A1="Google")+(B1="Stanford"),1,0)*C1

   =ЕСЛИ((A1="Google")+(B1="Stanford");1;0)*C1

   

   Explanation:

   • TRUE = 1, FALSE = 0.
   • For the first line, the function IF (IF) returns the following value:

     =IF(TRUE+TRUE,1,0)*3 ► =IF(2,1,0)*3 ► 3

     =ЕСЛИ(ИСТИНА+ИСТИНА;1;0)*3 ► =ЕСЛИ(2;1;0)*3 ► 3

     So a value of 3 will be taken into account.

   • For the second line:

     =IF(FALSE+FALSE,1,0)*5 ► =IF(0,1,0)*5 ► 0

     =ЕСЛИ(ЛОЖЬ+ЛОЖЬ;1;0)*5 ► =ЕСЛИ(0;1;0)*5 ► 0

     Thus, the value 5 will not be taken into account.

   • And for the third:

     =IF(FALSE+TRUE,1,0)*2 ► =IF(1,1,0)*2 ► 2

     =ЕСЛИ(ЛОЖЬ+ИСТИНА;1;0)*2 ► =ЕСЛИ(1;1;0)*2 ► 2

     That is, the value 2 will be taken into account, and so on.

4. Now all we’re missing is features SUM (SUM) that sums these values. Let’s add a function SUM (SUM) and replace A1 on A1: A8, B1 on B1: B8 и C1 on C1:C8.

   =SUM(IF((A1:A8="Google")+(B1:B8="Stanford"),1,0)*C1:C8)

   =СУММ(ЕСЛИ((A1:A8="Google")+(B1:B8="Stanford");1;0)*C1:C8)

   

5. Finish by clicking Ctrl + Shift + Enter.

   =SUM(IF((A1:A8="Google")+(B1:B8="Stanford"),1,0)*C1:C8)

   =СУММ(ЕСЛИ((A1:A8="Google")+(B1:B8="Stanford");1;0)*C1:C8)

   

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. They do not need to be entered by yourself. They will disappear when you start editing the formula.

5. Explanation:
   • Range (array of constants) created with a function IF (IF) is stored in Excel memory, not in worksheet cells.
   • The array of constants looks like this: {1;0;1;0;1;0;1;0}.
   • It is multiplied by C1: C8 и это дает {3;0;2;0;4;0;1;0}.
   • The last array of constants is used as an argument to the function SUM (SUM), giving a result of 10.
6. We can take one more step forward. For example, count the number of lines that contain “Google” and “Stanford”, or “Columbia”.

   =SUM(IF((A1:A8="Google")*(B1:B8="Stanford")+(B1:B8="Columbia"),1,0)*C1:C8)

   =СУММ(ЕСЛИ((A1:A8="Google")*(B1:B8="Stanford")+(B1:B8="Columbia");1;0)*C1:C8)