Aray Formula - Sum from a number of OR criteria

[Joakim N]

Hi,

Following array formula sums data for Apple OR Pear. However, it's quite static. For instance if I want to add Orange & Banana , I have to update my formula. Is it possible if I can give my criteria in a range (Yellow) and don't have to update formula?

{=SUM(IF((A:A="Apple")+(A:A="Pear");B:B;0))}

Capture.PNG

[jeffreybrown]

Try...

=SUMPRODUCT(SUMIF(A:A,D2:D13,B:B))

[Joakim N]

Great,

I modified it to so I can use as many criteria as I want:

{=SUMPRODUCT(SUMIF(A:A;OFFSET($D$1;1;0;COUNTA(D:D)-1;1);B:B))}

[jeffreybrown]

I don't think you need to enter this as an array formula as the Sumproduct is doing that part for you.

It also might be a good idea to stay away from offset as it's volatile.

=SUMPRODUCT(SUMIF(A:A,D2:INDEX(D:D,MATCH(REPT("z",255),D:D)),B:B))

[kvsrinivasamurthy]

Try this

=SUMPRODUCT((COUNTIF(D2:D13,A1:A12)>0)*(B1:B12))

[Glenn Kennedy]

Not a Good Idea....

1. OFFSET is volatile and the formula will recalculate everytime ANYTHING is changed on the sheet.

2. It isn't an array formula.

3. Array formulae and SUMPRODUCT don't like whole column ranges and will be slow.

[Joakim N]

Thanks all for your support!