D-functions for criteria-based summaries and CUBE functions for Power Pivot models. This page lists all 16 of them; each links to a full lesson with an example you can copy and a practice file.
CUBEKPIMEMBERCUBEKPIMEMBER returns a KPI property (value, goal, status, trend) from an OLAP cube or Power Pivot model. Cube…CUBEMEMBERPROPERTYCUBEMEMBERPROPERTY returns a property of a member, such as a product's category or a customer's city, from the…CUBERANKEDMEMBERCUBERANKEDMEMBER returns the n-th member of a set, e.g. the 1st, 2nd and 3rd best customers from a…CUBESETCOUNTCUBESETCOUNT counts the items in a set made with CUBESET. Cube functions read from a Power Pivot Data…DAVERAGEDAVERAGE averages a column for rows that meet the criteria range.DCOUNTDCOUNT counts numeric cells in a column for matching rows. Use DCOUNTA to count any non-blank cell instead.DCOUNTADCOUNTA counts non-blank entries in a field for records that meet the criteria; unlike DCOUNT it also counts…DGETDGET returns a single value from the row that matches — and, usefully, refuses to guess when more…DMAXDMAX returns the largest value in a column among matching rows. It worked in every Excel version long…DMINDMIN is DMAX’s twin: the smallest value among matching rows.DPRODUCTDPRODUCT multiplies the values of a field for matching records (rarely needed; e.g. compounding growth factors). Like all…DSTDEVDSTDEV returns the sample standard deviation of a field for matching records. Like all D-functions it uses a…DSTDEVPDSTDEVP returns the population standard deviation for matching records. Like all D-functions it uses a database range and…DSUMDSUM adds a column for the rows that match a criteria range on the sheet. It looks old-fashioned,…DVARDVAR returns the sample variance for matching records. Like all D-functions it uses a database range and a…DVARPDVARP returns the population variance for matching records. Like all D-functions it uses a database range and a…