Skip to content

Email Newsletter Challenge 17

This week’s challenge will be a little tricky and force you to think outside the box. Your goal is to calculate a conditional sum without using SUMIFS.

Think you’re up to the challenge? Download the spreadsheet challenge using the links below.

Challenge

Your task is to calculate the total number of Small Blue notebooks sold without using Pivot Tables or SUMIFS.

Solution – Excel

Use the following formula to calculate the total.

=SUMPRODUCT(--(B4:B13="Blue"), --(C4:C13="Small"), D4:D13)

To understand this solution, let’s first look at how the SUMPRDOCUT function works. The SUMPRODUCT function accepts ranges or arrays for each argument.

The function then multiplies each corresponding item to generate an array of result values.

The function then adds together the results to give you a final answer.

Now for the solution above, here is what’s actually happening. The first two arguments result in arrays of TRUE/FALSE values. TRUE each time the specified value matches a value in the range and FALSE each time it does not match.

After that, you have two negation operators applied to each array. This gives you an array of ones (for TRUE values) and zeros (for FALSE values).

Finally, with the last argument being the array of the Quantity Sold values, the SUMPRODUCT function works like normal, and you get back the sum of Qty Sold Numbers that meet both criteria.

And that is how the SUMPRODUCT formula can replace the SUMIFS formula in Microsoft Excel.

Solution – Google Sheets

Use the following formula to calculate the total.

=SUMPRODUCT(B4:B13="Blue", C4:C13="Small", D4:D13)

To understand this solution, let’s first look at how the SUMPRDOCUT function works. The SUMPRODUCT function accepts ranges or arrays for each argument.

The function then multiplies each corresponding items in each array to generate an array of result values.

The function then adds together the results to give you a final answer.

Now in the solution above, here is what’s actually happening. The first two arguments result in arrays of TRUE/FALSE values. TRUE each time the specified value matches a value in the range and FALSE each time it does not match.

Now in Google Sheets, TRUE values equate to one, and FALSE values equate to zero. And so, with the last argument being the array of the Quantity Sold values, the SUMPRODUCT function works like normal, and you get back the sum of Qty Sold Numbers that meet both criteria.

And that is how the SUMPRODUCT formula can replace the SUMIFS formula in Google Sheets.

I hope you had fun, felt challenged and learned something new!

And if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 2 Comments

  1. Dang… I’m learning new things all the time here! This was tough as nails though! I can appreciate the challenge! Keep it up!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top