use array formula with or function

1. 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.  Register To Reply

2. 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.  Register To Reply

3. 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  Register To Reply

4. 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   Register To Reply

5. 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.  Register To Reply

6. Re: use array formula with or function Originally Posted by cbatrody =SUMPRODUCT(SUMIF(G3:G11,B3:B5,E3:E11))
Even better!  Register To Reply

7. Re: use array formula with or function

Thanks XOR!  Register To Reply

8. 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.  Register To Reply

9. Re: use array formula with or function Originally Posted by ronga I'm still wondering why my original approach did not work.
OR only ever returns a single value, never an array of values.

Regards  Register To Reply

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