Excel: combining IF formula with FREQUENCIES and MATCH-CodePudding

I'm trying to compbine an if-then and a frequency formula in Excel:

I need to get the sum of the values in column C1:C5 in cell C6, but only there is a match with the value in B1:B5 and A6.

So in the picture below, the result should be 25 (Tthe formula is in Dutch, but I'm using IF, SUM, FREQUENCY, MATCH)

CodePudding user response：

Enter =SUMIF($B$1:$B$5,A6,$C$1:$C$5) or the Dutch equivalent into C6 and fill down.

CodePudding user response：

The one which you were trying shall render you only SUM of UNIQUE values i.e. for 1,2 or 3. However there is a workaround instead of using MATCH Function simply use the FREQUENCY Function with IF & ROW Function & wrap it within SUMPRODUCT!

Perhaps there is much easier way to of summing instead of using FREQUENCY Function, FREQUENCY Function also slows down the working functionality.

Using FREQUENCY Function --> Formula used in cell C8

=SUMPRODUCT(FREQUENCY(IF($B$1:$B$5=$A6,ROW($C$1:$C$5)),ROW($C$1:$C$5)),$C$1:$C$6)

The easy way is using SUMPRODUCT Function this is because it multiplies ranges or arrays together and returns the sum of products. SUMPRODUCT is an incredibly versatile function which can be further used to count and sum like COUNTIFS or SUMIFS, but with more flexibility. Other functions can also easily be used inside SUMPRODUCT to extend functionality even further.

Formula used in cell C7

=SUMPRODUCT(($B$1:$B$5=$A$6)*($C$1:$C$5))

Side Note: The SUMPRODUCT function can be used to create array formulas that don't require CTRL SHIFT ENTER. This is the most important reason why SUMPRODUCT is been so extensively used to create more advanced formulas.

One main issues with array formulas is that they usually return incorrect results if they are not entered with CTRL SHIFT ENTER. That said if someone forgets to confirm press CSE when entering a formula, the expected output may suddenly alter, nonetheless the actual formula did not change. Using SUMPRODUCT means the formulas will work in any version of Excel without special handling.

In O365, the formula engine handles arrays natively & can often use the SUM function in place of SUMPRODUCT in an array formula with the same desired output and you don't have to press CSE. If the same formula is opened in an earlier version of Excel, it will require CTRL SHIFT ENTER.

Fundamentally SUMPRODUCT is a much safer option if a worksheet is used any version of Excel before MS365, even if the worksheet was created in MS365.