+ Reply to Thread
Results 1 to 9 of 9

use array formula with or function

  1. #1
    Registered User
    Join Date
    10-07-2015
    Location
    Manila
    MS-Off Ver
    Windows 7
    Posts
    2

    use array formula with or function

    I have months in column G in numerical form, and amounts to be summed in column E. I want to find out the sum of amounts for the third quarter (months = 7, 8, 9) using an array formula: {=+SUM(IF(OR(G:G=7,G:G=8,G:G =9),E:E,0))}. The formula doesn't work, though, it is summing all the entries in column E.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,869

    Re: use array formula with or function

    Try this & change the ranges as required

    =SUM(IF($G$1:$G$30={7,8,9},$E$1:$E$30,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Cheers!
    Deep Dave

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,598

    Re: use array formula with or function

    You can also make a non-CSE version of NeedForExcel's CSE solution:

    =SUMPRODUCT(($G$1:$G$30={7,8,9})*$E$1:$E$30)

    Also, be strongly advised not to use entire column references within an array formula.

    Unlike some functions, e.g. COUNTIF(S), SUMIF(S), with which the use of entire column references has virtually no detriment to calculation performance, array-processing functions must calculate over all cells passed to them. And that applies equally to those cells which are beyond the last non-empty cell in the range being passed.

    So if, for example, you only have data extending as far as row 1000, then, by using entire column references in such a formula, you are forcing Excel to calculate more than one million cells beyond that which is actually necessary, an astonishing amount for a single formula.

    As such, it is important to either choose a suitably low - though sufficient - value for the upper row reference, or else make your ranges dynamic, such that the upper row reference is determined via a function which automatically detects the last-used cell within the column, thus offering maximum efficiency.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,869

    Re: use array formula with or function

    You can also make a non-CSE version of NeedForExcel's CSE solution
    Which is always better than a CSE Formula.. I have a very bad habit of writing CSE Formulas, even when non-cse can be written.. Need to get rid of the habit ASAP

  5. #5
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: use array formula with or function

    OR,

    Try the following if you do not wish to type the lookup values within {}:

    =SUMPRODUCT(SUMIF(G3:G11,B3:B5,E3:E11))

    See the attached file.
    Attached Files Attached Files

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,598

    Re: use array formula with or function

    Quote Originally Posted by cbatrody View Post
    =SUMPRODUCT(SUMIF(G3:G11,B3:B5,E3:E11))
    Even better!

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: use array formula with or function

    Thanks XOR!

  8. #8
    Registered User
    Join Date
    10-07-2015
    Location
    Manila
    MS-Off Ver
    Windows 7
    Posts
    2

    Re: use array formula with or function

    Thanks, that did the trick but I'm still wondering why my original approach did not work. And yes, I did enter it as an array formula.

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,598

    Re: use array formula with or function

    Quote Originally Posted by ronga View Post
    I'm still wondering why my original approach did not work.
    OR only ever returns a single value, never an array of values.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. array formula into a function
    By John ThePriest in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2015, 09:36 AM
  2. Array Formula Using the Sum Function
    By kingjams in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2007, 07:19 PM
  3. Using AND function within an array formula
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 07:05 PM
  4. Using AND function within an array formula
    By Andrew L via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  5. [SOLVED] Using AND function within an array formula
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 11:05 AM
  6. Using AND function within an array formula
    By Andrew L via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. [SOLVED] Using AND function within an array formula
    By Andrew L via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1