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"


Comments

  1. App Development MumbaiFebruary 3, 2017 at 6:15 PM

    Hey keep posting such good and meaningful articles.

    ReplyDelete
  2. GST Training DelhiOctober 9, 2017 at 11:03 AM

    Nice post, things explained in details. Thank You.

    ReplyDelete
  3. App Devlopment CompanyJanuary 12, 2018 at 4:12 PM

    Hi, extremely nice effort. everybody should scan this text. Thanks for sharing.

    ReplyDelete
  4. Jason492August 19, 2021 at 8:08 PM

    On the Secrets of a Sugar Daddy podcast there are True life podcasts my guests and I will pull back the curtain and give you first hand accounts of our experiences. We will cover a wide range of topics from building the right kind of profile , to avoiding the wrong kind of people

    ReplyDelete
  5. ShefFebruary 3, 2023 at 6:47 PM

    Pretty amazing article. I recently found your website and just wanted to expressipvanish coupon code how much I've loved reading your thoughts. Whatever the case, I'll be checking back, and I hope you'll publish again soon.

    ReplyDelete

Post a Comment

Popular posts from this blog

Keyboard Shortcut to add/delete rows or columns in Excel

Image
" Ctrl + + " (add rows or columns) and " Ctrl + - " (delete rows or columns) are the easy shortcuts one may remember while adding and deleting rows/columns in Microsoft Excel. Adding rows or columns: If you wish to add rows above the current cell... just press "shift + spacebar" (discussed in my earlier post) to select the current row and then press "Ctrl + +". It will insert a row over the current row. Press "Ctrl + +" repeatedly to insert as many rows. Similarly, "ctrl + spacebar" and then "Ctrl + +" adds a column before the current column. You may try pressing "Ctrl + +" without selecting a row or a column. In such a case, you may get a pop-up (as shown in the image) where you can choose an option of adding a row or a column. Deleting rows or columns: "shift + spacebar" followed by "Ctrl + -" deletes the selected row & "ctrl + spacebar" followed by "Ct
Read more

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
Read more

Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)

Image
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:  =SU
Read more