Excel use If condition on aggregate Function using Array

This Excel tutorial explains how to use If condition on aggregate Function using Array such as Average, Median, Mean, Maximum, Minimum.

Excel use If condition on aggregate Function using Array

Some Excel formula require you to input a range cells as argument in order to calculate a value, such as Sum, Count, Average, Median, Mean, Maximum, Minimum.

However, in those formula, you cannot use If Condition on the data Range before calculating Sum, Count, Average, Median, Mean, Maximum, Minimum.

For example, the below Function will not work correctly

=SUM(IF(A10:A19="A",B10:B19))

Example – Use Array to apply IF condition on Sum, Median

Use the below data as an example.

If you try to sum the data for Category A, you can apply SUMIF.

However, there is an alternative way to do that using Array.

=SUM(IF(A10:A19="A",B10:B19))

In the above formula, the brackets represent Array. Array does not work if you type directly in keyboard. After typing the formula inside , press CTRL+SHIFT+ENTER to turn the formula  into Array, will be added automatically.

Instead of evaluating whether A10:A19=”A” as a whole, Array evaluates whether A10=”A”, A11=”A”,A12=”A”, A13=”A”… individually and return the respective B10:B19 value if TRUE, finally Sum all the returned numbers.

Below is the evaluation process of Array:

=SUM(IF(A10:A19="A",B10:B19))
=SUM(IF(TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE,B10:B19))
=SUM(1,5)
=6

Similarly, you can apply If Condition on other statistical Functions or aggregate Functions. Take Median as another example.

To find the Median of Category A

=MEDIAN(IF(A10:A19="A",B10:B19))
=MEDIAN(IF(TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE,B10:B19))
=MEDIAN(1,5)
=3

To find the Maximum of Category B

=MAX(IF(A10:A19="B",B10:B19))
=MAX(IF(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE,B10:B19))
=MAX(4,3,1)
=3

You can even combine AND / OR to evaluate in Array

=MAX(IF(OR(A10:A19="C",A10:A19="B"),B10:B19)

Outbound References

https://www.youtube.com/watch?v=DSivs93UDgc

 

Excel use If condition on aggregate Function using Array