Highline Excel Class 39: Forcing Functions To Become Arrays

If an array or range is put into a function argument that is expecting a single value, the function becomes an array and delivers an array of values instead of a single value. See these amazing tricks:
1)Count Unique values with SUMPRODUCT & COUNTIF functions array formula
2)Lookup Adding with SUMPRODUCT &SUMIF functions array formula
3)MEDIAN IF functions in array formula (calculating Median with conditions (criteria))
4)MAX IF functions in array formula (calculating maximum value with conditions (criteria))
5)MIN IF functions in array formula (calculating minimum value with conditions (criteria))
6)Adding (summing sum) top three values using SUM & LARGE functions with an array in array sytax
7)Conditional (criteria) adding & counting for dates with the TEXT & SUMPRODUCT functions
This is a beginning to advanced Excel class taught at Highline Community College by Mike Gel excelisfun Girvin Busn 214 BTech 109

Category:

Education

Tags:

• Excel
• Array
• Formulas
• Functions
• SUMPRODUCT
• COUNTIF
• SUMIF
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[jefficy1]

thx! its getting harder and interesting!

[ExcelIsFun]

@jefficy1 , harder is good - it means you can learn more.

[amitmend]

Hi,

I would like to know why did you entered the "sumif" function in the way where the range is actually the criterion. As far as I know, the range should have been B21:B25.

I cant understand the logic behind this, and i will be more than happy if you can try to explain this to me.

(the time frame is 08:20-08:30)

[ExcelIsFun]

@amitmend , the logic is that instead of giving the criteria SUMIF argument just one criterion we are giving it many criteria, this is why when I evaluated the formula in the video it showed that the SUMIF delivered more than one value. If you put more than one value (range of cells) into the criteria argument, you will always get a result that is more than one value - because the normal way that SUMIF works (1 criteria), it yields just one value.

[ExcelIsFun]

@amitmend , this is the whole idea of this video - forcing functions to be arrays!

[SanadFeisal]

Now I know what an array is! Thank you sir!

[ExcelIsFun]

You are welcome!

[amitmend]

Thank you for your quick response.

The thing is that I didn't understand why you wrote the sumif function like this

sumif(e22:e24,b21:b25,f22:f24)

and not like this

sumif(b21:b25,e22:e24,f22:f24

isn't the criteria needs to be the "part1,part2,part3" and the range- column B?