Page 36 - Finanancial Management_Feb_Apr23

PivotTables Fields pane Regular readers will know

that SUMPRODUCT is one

of my favourite functions

in Excel. … The function is

highly underestimated.

This list will be sorted alphanumerically by default. Now,
we simply count the number of nonblank items in this list. If
you had a blank item in your original list, don’t worry, it will
still appear in the PivotTable as (blank) so will be treated as,
er, nonblank (see the screenshot “Using PivotTables to Count
Unique Items in a List”).
There are a couple of drawbacks with this approach:
1. You should note that the COUNTA formula may need to
include a larger range than is filled by the PivotTable. This
is in case the range extends when the data is refreshed.
This may cause issues if end users add other data to this
worksheet.
2. If the source data changes, the PivotTable must be
refreshed: The COUNTA formula will not necessarily
provide the right answer until this action is performed.
This produces the following PivotTable: Many users forget to do this.
Therefore, the idea is simple. However, although it will
work in all current versions of Excel, end users may forget to
refresh the data should the source list be updated. So, what
alternative do we have?
Option 3: Using SUMPRODUCT
Regular readers will know that SUMPRODUCT is one of my
favourite functions in Excel, so much so that I named our
company after it.
The function is highly underestimated. Consider the
example in the screenshot “Example Use of the SUMPRODUCT
Function”.
Using PivotTables to count unique items in a list

FM-MAGAZINE.COM February 2023 I FM MAGAZINE I 35