Page 38 - Finanancial Management_Feb_Apr23

Here, I have various pricing points and the The downloadable Excel file
corresponding quantities sold. To calculate my total sales,
I can compute my sales by taking the product of Unit Price details the three alternatives.
multiplied by Quantity on a line-by-line level and then
summing them. As you can see, SUMPRODUCT does it all
in one go:
Here, I have used the formula
=SUMPRODUCT(B3:B11,C3:C11)
=SUMPRODUCT((Example3<>””)/COUNTIF(Example3,
But SUMPRODUCT is more powerful than that. The Example3&””))
formula
Clearly, this seems to work, although its logic may be a
=SUMPRODUCT(B3:B11*C3:C11) little less transparent than the other two approaches upon first
glance. The initial condition:
does exactly the same thing. However, consider
(Example3<>””)
=SUMPRODUCT(B3:B11/C3:C11)
checks whether the range Example3 contains nonempty cells
Take a look at this revised example in the screenshot (TRUE if so, FALSE otherwise). Here, it does not need to be
“Revised Example of the SUMPRODUCT Function”. nonblank — it just needs to be anything that cannot occur in
Do you see how SUMPRODUCT divides on a the list in this scenario. You could just substitute this for one
record-by-record basis? This is powerful, and it is this [1] should you wish, but I wanted to demonstrate how this
concept that I shall use for our final method to be employed, might work if you wished to exclude blank cells. This gives us:
using our list now conveniently called Example3 (see the
screenshot “Using SUMPRODUCT to Count Unique Items in TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE,
a List”). TRUE, TRUE

Using SUMPRODUCT to count unique items in a list

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