# 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.

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.

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

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

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.

6. ## Re: use array formula with or function

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

Thanks XOR!

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.

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

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