Conditional SUM with Excel SUMPRODUCT function – Intro + Examples

Putting it in simple words it is a conditional sum where you want to sum values on a condition if they are paid or alternatively if date is mentioned in “Paid On” column. If cell is empty then leave the respective value alone.

To practice this tutorial download this excel workbook and apply the concepts easily. Workbook contains two sheets, Practice and Solved. Practice sheet is where you will apply the things you learn below. Solved worksheet contains the final product of this tutorial.

Following is sample data:

View Contents [hide]

• Using layman approach – Learn to crawl if you want to RUN!
• Introducing another product of SUM family
• Getting Excel PRO in 3-2-1!
• Learning a product of SUM curiosity, SUM determination, SUM belief
• “But hey! you have NOT mentioned what is NOT doing there?”
• Mysterious Dashes!
• Bonus Tip #1 – SUM amounts that were paid on August 06, 2013
• Bonus Tip #2 – SUM all invoices with due dates before August 06, 2013

Using layman approach – Learn to crawl if you want to RUN!

1. Approach is simple, if date is mentioned i.e. cell is not blank (FALSE) then include the figure in sum. If date is not mentioned i.e. cell is blank (TRUE) then exclude the value from sum.
2. Secondly workout a way to get 1 for FALSE and 0 for TRUE.
3. Thirdly, multiply the values with 1s and 0s. This will leave us with values against which date is mentioned. For others we will have zero.
4. Sum the values together.

Lets get it done in Excel now

Step 1: Open the workbook and make “Practice” worksheet active. In cell G2 put a formula: =ISBLANK(F2). Hit Enter. This formula checks “Is cell F2 blank?” The answer is either TRUE or FALSE. In our example F2 is blank so the value is TRUE. Double click the fill handler to copy formula down the column.

Step 2: With True and False done, now we want 1s and 0s. To get this put this formula in cell H2: =IF(G2=TRUE,0,1). Hit Enter key. Double click the fill handler to get the formula pasted down the column for the rest of data range.

So we have 1s and 0s as well. Let’s multiply each amount with 1s and 0s. This will leave us with only those values that are multiplied with 1 or in other words for which date is mentioned. Put this simple formula in cell I2: =D2*H2. Double click the fill handler to populate rest of the cells with the relative formula.

Now you can observe that you are left with only those amounts for which date is mentioned. For those without date are 0. Now its a simple SUM formula’s job to add the values.

In cell E22 put this formula: =SUM(I2:I18)

You are done! Bravo you pulled off a great feat without knowing more than SUM function in Excel because you are good at thinking and finding solutions even with almost no tools to do the job.

But Excel is about efficiency. Although we did it but it took us long time to get to the real job. So there must be an easy way to get same thing done. And yes there is! This can be done easily in Excel if you know one of the smart sibling of SUM function – SUMPRODUCT

Introducing another product of SUM family

Syntax of SUMPRODUCT formula is:

SUMPRODUCT(array1,[array2],[array3],….)

In this formula you have to mention at least one array, but you can put multiple arrays together but additional are optional and that is why they are mentioned in square brackets. Remember square brackets are just to represent that they are optional and in real formula re not required.

Some of you might have heard a word array in context of Excel but it holds a whole word in itself. To unlock some real cool powers of Excel one must learn array formula and their applications.

In normal language array means collection of item arranged in specific or systematic order. Same is the case in Excel.

SUMPRODUCT – In lame words

SUMPRODUCT sums the product of values. Product is multiplication operation. And this is exactly what we did above. We summed the values that were the result of product. So basically SUMPRODUCT let us do all that what we did above using many columns and formula. Boy! this formula is amazing.

Getting Excel PRO in 3-2-1!

In cell E23 put the following formula and press Enter key.

=SUMPRODUCT(D2:D18,–(NOT(ISBLANK(F2:F18))))

AMAZED? 😀 Let’s understand how this formula works

Learning a product of SUM curiosity, SUM determination, SUM belief

Before we move forward lets learn a fine little shortcut F9. In edit mode, having some formula in formula bar, if you want to know the result of formula even before you hit Enter button you can use this shortcut. Just select the formula and press F9 key. It basically executes the formula and present you with the result. Give you first try on any of the formula we used in the layman’s approach above.

For example select cell E22 and hit F2 key. Formula is: =SUM(I2:I18). Select the formula and press F9 it will give you the solved value.

Now let’s use this formula on the complex SUMPRODUCT formula we used part by part. Following steps will help you understand the story:

Step 1: Select cell E23 and press F2 key to get in edit mode.

Step 2: Select ISBLANK(F2:F18) part of the formula. Carefully select the relevant parenthesis as well they count a lot! Press F9 and what the true mother of Excel! it has true and falses running just like we did above. Remember in our layman approach we applied exactly this formula? Well this formula is doing the exact same job here! But as a part of formula. Hit Ctrl+Z to return to original state.

Step 3: While in the edit mode, select: –(NOT(ISBLANK(F2:F18))) and press F9 and you will get the trail of 1s and 0s. Remember in layman’s approach we converted TRUEs and FALSEs into 0s and 1s. This is exact same thing done here. How nifty is that. With a combo few formula we achieved the exact same thing we did in

“But hey! you have NOT mentioned what is NOT doing there?”

We understood twice already that ISBLANK gives True and False. The problem is ISBLANK = True means Empty and in Excel’s language true means 1. But we want 0 for true i.e. if cell is empty then give 0 so that respective amount becomes 0. To make True to False we added NOT function as it reverses the logic. To understand better select: ISBLANK(F2:F18) part of the formula and press F9. You get:

{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE}

Hit Ctrl+Z and now select: NOT(ISBLANK(F2:F18)) part of the formula and press F9 key you will get:

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}

The second is exactly the opposite of the first. And that is what wanted.

Just in case if you are asking where we did this in layman’s approach, we reversed the order when we applied the IF formula to 1s and 0s. In that function we put the logical test as IF value is TRUE then 0 otherwise 1.

Mysterious Dashes!

And surely they are. The problem is we cannot multiple TRUE and FALSE directly with the numbers. We have to turn then in 1s and 0s to have the product. To get this, we place two dashes and this converts TRUE to 1 and FALSE to 0. And that is the reason when you select: –(NOT(ISBLANK(F2:F18))) part of the formula and hit F9 you get:

{0;1;0;0;0;0;0;1;0;0;0;1;0;0;0;1;0}

Now the rest of the operation is simple to understand. Formula will find the product of values and then calculate the sum to give us the value.

Bonus Tip #1 – SUM amounts that were paid on August 06, 2013

Using the same approach with little modification to the formula we can find the sum of the invoices that are paid on particular date e.g. August 06, 2013

I calculated the sum in cell H7 and used following formula:

=SUMPRODUCT(D2:D18,–(F2:F18=DATE(2013,8,6)))

Replacing NOT(ISBLANK( combo with range of “Paid On” column and finding the value which is equal to August 6, 2013, was a simple task. Understand one thing that as it is an array formula therefore were able to compare date with the range without any problem.

Bonus Tip #2 – SUM all invoices with due dates before August 06, 2013

In cell H8 I put the following formula to get this job done:

=SUMPRODUCT(D2:D18,–(E2:E18<DATE(2013,8,6)))

In this formula logical test is conducted on data range of DUE DATE column and checked for dates before August 6 and then the respective amounts are added.

And this way you get promoted to Excel PRO level after learning conditional SUM in Excel.

Let me know what other approach you would have used to get the same result done. Learning a different approach will be fascinating. You can share your ideas in the comment box below .

#quantriexcel #kynangmoi
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1