Looking for more? Try chatting with the Exceljet Chatbot.
The MODE function is fully automatic and will return the most frequently occurring number in a set of numbers. For example:
=MODE(1,2,4,4,5,5,5,6) // returns 5
In the example shown, we give MODE the range B4:K4, so the formula is solved...Read more
The core of this formula is the IF function, which "filters" the names in the table by color like this:
IF(group=E5,name,""))
The logical test checks each cell in the named range "group" for the color value in E5 (red in this case). The...Read more
Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted:
$B3=$E$5
The second test is more complex:...Read more
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an elegant and versatile function, which this example illustrates nicely.
In this example, SUMPRODUCT is...Read more