Could you please help me understand the Excel formula

Question

I am using the excel report that includes formulas was designed long back, since last few days i started facing issue with the formula in one the cells in the excel report.

The formula used is :

=SUM(('Defects Past SLA'!D4:D72="1 - High")*('Defects Past SLA'!P4:P72="Zero"))  

'here 'Defects Past SLA' is sheet name ' In that sheet, column D has few cells with value "1 - High" and P column has few cells with value "Zero".

My only question is how Sum function is working here, could you please help me understand above formula. I tried a lot but could not make out what it meanse. Thanks for your help.

Answer 1

This SUM actually counts all the rows where the column D of that sheet contains 1 - High and where the column P of the same row contains Zero.

Here's an example with a smaller table:

A    B
1    0
0    1
0    0
1    1

If I do:

=SUM((A:A=1)*(B:B=1))

I will get the result of 1

(A:A=1) gives an array of TRUE and FALSE. When applied to the data above, we get:

A 
1  => True
0  => False
0  => False
1  => True

Substituting that in the formula, we get:

=SUM((True; False; False; True)*(B:B=1))

The same thing happens to column B:

B 
0  => False
1  => True
0  => False
1  => True

Substituting that in the formula, we get:

=SUM((True; False; False; True)*(False; True; False; True))

Now, True is the same as 1 in excel, whilst False is the same as 0. This means, we can rewrite the above as:

=SUM((1; 0; 0; 1)*(0; 1; 0; 1))

This is a multiplication of arrays. First term of the array will be multiplied by the first term of the next array so that we have:

1 x 0 = 0
0 x 1 = 0
0 x 0 = 0
1 x 1 = 1

This means that if we substitute the results in the above formula, we get:

=SUM((0; 0; 0; 1))

And the sum of these values is 1.

---

Note that the equivalent formula for your given situation would be:

=COUNTIFS('Defects Past SLA'!D4:D72,"1 - High",'Defects Past SLA'!P4:P72,"Zero")