Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)

We already know the primary challenge of using SUMIF or SUMIFS i.e. unable to sum more than a single column or row as a sum range. Many of us in the course of our excel usage may have experienced that SUMIFS has a bigger disadvantage when used across workbooks i.e. the source workbook should be open inorder to enable to computation in the destination workbook. If the source workbook remains closed, the result in the destination workbook will be "#VALUE".

In my previous post, the alternative suggested not only does overcome the "#VALUE" challenge but also allow users to sum a larger range. In this post, I was able to decipher yet another alternative. I'm sure expert users may already be using this however to help other users, this post can be a ray of hope.

We may have used the SUMPRODUCT function to calculate weighted average. Extrapolating the usage of SUMPRODUCT with condition or conditions can help you achieve the SUMIFS goal. 

Syntax: 

=SUMPRODUCT( (Condition1) * (Condition2) * (ConditionN) * (sumrange))

The table below is an example where in we can compute the total of Range1 and Range2 based on a combination of Criteria1 and Criteria2.

Source Table (may or may not be in the same Workbook)

Suppose, we need to find the sum of the Range1 and Range2 if Criteria1 is "A" and Criteria2 is "w" let us see the syntax of the SUMPRODUCT function to be used.

If is it is in the same file in Sheet1:

=SUMPRODUCT((Sheet1!$B$2:$B$9=B2)*(Sheet1!$C$2:$C$9=C2)*Sheet1!$D$2:$E$9)

if is is in a different file (named "Sample.xlsx") in Sheet1

=SUMPRODUCT(([Sample.xlsx]Sheet1!$B$2:$B$9=I18)*([Sample.xlsx]Sheet1!$C$2:$C$9=J18)*[Sample.xlsx]Sheet1!$D$2:$E$9)

This will give you the identify the values against the combination of "A" and "w" and give the result as 14.

In this case you don't have to use the array feature unlike my previous post.

Hope this helps you in using your excel workbooks more efficiently and flawlessly.

Excel Keyboard Shortcuts - Handy during day to day work

I've had few posts earlier on few excel shortcuts.

• Navigation powered by "Ctrl" in Excel
• Shortcut to insert current date in a cell
• Shortcut to AutoFilter

So, here I am, back with some more interesting shortcuts which will make your excel spreadsheet experience simpler.

Let me start with some "CTRL" combinations which will be very handy for formating cells to match your requirements:

• CTRL + 1 : Opens the "Format Cells" dialog box
• CTRL + 2 : is to make the contents of the cell BOLD (this is a toggle key, to unbold use same CTRL + 2)
• CTRL + 3 : is to make the contents of the cell ITALICS (this is a toggle key)
• CTRL + 4 : is to underline the contents of the cell (this is a toggle key)
• CTRL + 5 : is to strike through the contents of the cell (this is a toggle key)
• CTRL + 9 : is to hide the entire row (to unhide use SHIFT + CTRL + 9)
• CTRL + 0 : is the hide the entire column (to unhide use SHIFT + CTRL + 0)
• CTRL + ~ : is to show or hide formula (this is a toggle key)
• CTRL + - : brings up the delete cells dialog box
• CTRL + + : brings up the insert cells dialog box

Another set of shortcuts is with the combination of SHIFT and CTRL:

• SHIFT + CTRL + 1 : converts the number in the cell to decimal format with 2 decimal places
• SHIFT + CTRL + 2 : converts the number in the cell to time format (your default system format)
• SHIFT + CTRL + 3 : converts the number in the cell to date format (your default system format)
• SHIFT + CTRL + 4 : converts the number in the cell to currency format (your default system format)
• SHIFT + CTRL + 5 : converts the number in the cell to percentage format
• SHIFT + CTRL + 6 : converts the number in the cell to scientific format (your default system format)
• SHIFT + CTRL + 7 : adds outline border to the selected cell(s)
• SHIFT + CTRL + - : Removes the border from the select cell(s)

Get used to these common shortcuts till I manage to compile few more.

You can find more such posts on the internet. So make sure you refer and make best use of the shortcuts.

Alternative for SUMIF and SUMIFS

Ever wondered while using "SUMIF" function, the sum range cannot be more than one column!! This indeed is a big drawback while using a large data set where you need to sum matching certain conditions.

I often end up using multiple sumif with "+" which makes the formula very long. So was thinking of some alternative that can make life easier.

Thanks to the search engines these days, I did come across a workaround.

Refer to the above image:

In the above example, I have a table with 4 columns where H1 and H2 are identifiers. H3 and H4 is the data which needs to be summed up.

Condition 1 (alternative for SUMIF):
I need to sum values of H3 and H4 where the identifier H2 is "Y".
The formula will be =SUM(IF(B2:B5=B9,C2:D5)) followed by Ctrl + Shift + Enter which will make it a array formula. The final formula will look like this: 
{=SUM(IF(B2:B5=B9,C2:D5))}

Condition 2 (alternative for SUMIFS):
I need to sum values of H3 and H4 where the identifier H1 is "N" and H2 is "Z".
The formula will be =SUM(IF(A2:A5&B2:B5=B10,C2:D5)) followed by Ctrl + Shift + Enter which will make it a array formula. The final formula will look like this: 
{=SUM(IF(A2:A5&B2:B5=B10,C2:D5))}

Try this out in a larger data set and check your results.


Disclaimer: This will work only in a array i.e. without "Ctrl + Shift + Enter" , the result may be "#N/A"

Solving issues with "VLOOKUP"

VLOOKUP syntax

We often encounter "#N/A" while using VLOOKUP function to compare two lists. One of the most common problems that give rise to this error is extra spaces either to the end or to the beginning of the string.

What's the way out?

The simplest way out is to use "TRIM" function as a sub function.

Example

While trying to use VLOOKUP in the normal course, the result will be "#N/A", whereas with TRIM nested in the VLOOKUP function will give the positive result.

(TRIM function removes the unwanted spaces in the beginning and end of a string.)

Identify duplicate values in a list using "Countif"

Though MS Excel today allows to easily eliminate duplicate values in a list, have you ever tried to identify the number of entries that gets repeated in an excel list?

In the above example, we can identify the repeat entries using "COUNTIF" function.

Option 1

formula: =COUNTIF($D$1:D1,D1)
Explanation: This formula counts the occurrence of the value in the list i.e. on the first occurrence, the result will be one and on the second it will be 2 and like wise. (see the figure below)

Option 2

formula: =COUNTIF($D$1:$D$8,D1)

Explanation: This formula counts the total number of repetitions. If an entry appears thrice in a list, then the result will be 3 in all the occurrences of that entry. (see the figure below)

So start using whichever is convenient to you... :)