by Ilker | Mar 26, 2020 | Excel Tips & Tricks
The COUNTIFS and COUNTIF functions can count the values in ranges which meet certain criteria. Although the COUNTIFS function accepts multiple range-criteria pairs, it doesn’t allow using different size ranges. You can overcome this with the help of the SUM and...
by Ilker | Nov 19, 2019 | Excel Tips & Tricks
Control Shift Enter Excel shortcut, as known as CSE, is a key combination you can use to apply array functions.CSE abbreviation is also used for array formulas that are entered using the Control + Shift + Enter key combination when entering a formula. Array formulas...
by Ilker | Mar 28, 2019 | Excel Formulas
The SUMPRODUCT Excel formula is a Math & Trig function that multiplies the values in arrays, and returns the sum of products. Although this sounds like something you could do using a combination of the SUM and the PRODUCT formulas, there is more to it! Thanks to...
by Ilker | Feb 21, 2019 | Excel Formulas
With the dynamic arrays concept, formulas that return an array can spill the results to the neighboring cells automatically. However, this behavior conflicts with legacy implicit intersection formulas that use ranges (arrays). For the sake of backward compatibility,...
by Ilker | Feb 21, 2019 | Excel Formulas, Excel Tips & Tricks
With the introduction of the dynamic arrays and spill concepts, Microsoft has also released a spill range operator (#). This operator is designed to reference spill ranges dynamically.For example, the cell D3 has =UNIQUE(B3:B16) formula which spills the return values...
by Ilker | Feb 21, 2019 | Excel Tips & Tricks
The term spill in Excel’s literature is defined as populating multiple cells with a single formula. With the announcement of the dynamic arrays in September 2018, Excel formulas can now return (spill) an array of values into neighboring cells by starting with...
by Ilker | Feb 20, 2019 | Excel Tips & Tricks
Dynamic arrays can populate a range of cells from the values of an array. With dynamic arrays, any formula that returns an array of values will spill the results into the adjacent empty cells automatically. You can create tools like interactive pricing applications...
by Ilker | Feb 20, 2019 | Excel Formulas
The Excel SINGLE function is a Lookup & Reference formula that returns a value using implicit intersection. In this guide, we’re going to show you how to use the Excel SINGLE function and also go over some tips and error handling methods. The Dynamic Array...
by Ilker | Feb 20, 2019 | Excel Formulas
The RANDARRAY function is a Math & Trigonometry formula that creates an array of randomly generated numbers. In this guide, we’re going to show you how to use the RANDARRAY function and also go over some tips and error handling methods. The Dynamic Array functions...