sum: "or" condition in sum formula?

[joshuadonner]

I'm using a variation on the following formula to sum values from a
particular column for records that meet 3 criteria:

=SUM(IF(Range1=Value1,IF(Range2=Value2,IF(Range3=Value3,RangeToSum,0),0),0))

in other words:

A B C D
1 1 3 5 7
2 0 4 5 8
3 2 3 5 9
4 2 4 6 10
5 2 3 6 11

{=SUM(IF(A1:A5=2,if(B1:B5=3,if(C1:C5=5,D15,0),0),0))} = 9

But what i want to do is make one of the conditions allow multiple
values. For example, in the above example, instead of only counting
the record if column A = 1, I want to count the record if Column A = 1
or 2. Or something like:

'{=SUM(IF(OR(A1:A5=1,A1:A5=2),if(B1:B5=3,if(C1:C5=5,D15,0),0),0))} =
7+9 = 16

The values in the real data for column A are code numbers, so I can't
use logical operators like "if(A1:A5>1,,)"

Any guidance on how to approach this would be most welcome. (maybe i'm
going about the whole thing wrong...?!)

thanks,

josh

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((A1:A5={1,2})*(B1:B5=3)*(C1:C5=5)*(D15))

 

[joshuadonner]

frank -

that worked great, thanks!!

(once i figured out that that the array feeding into the column
statement had to be a row and not a column...)

-jos

 

[Frank Kabel]

Hi
you may have a look at:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for more about this. You could also use a formula such as:
=SUMPRODUCT(--(ISNUMBER(MATCH($A$1:$A$10,X1:X3,0))),--(B1:B10=3),--(C1:
C10=5),D110)

where X1:X3 store your values to check for column A

 

[joshuadonner]

Frank -

Turned out that my nested if formula works, the problem was that
needed to transpose my lookup array.

So my questions now is, which approach is "better"?

=SUM(IF(A1:A5=LookupArray,if(B1:B5=3,if(C1:C5=5,D15,0),0)
,0))

=SUMPRODUCT((A1:A5=LookupArray)*(B1:B5=3)*(C1:C5=5)*(D15))

When i'm all done, i'm going to have this formula about 10,000 times
so i'm a little sensitive to how long it takes each time i recalculate
I tested a bit and didn't notice a speed difference between the two
but maybe you or someone has an opinion before i start copying one o
these all over?

thanks again!!

-jos

 

[Frank Kabel]

Hi
see my otgher post. Using MATCH should be faster

 

[joshuadonner]

you were right - the match was faster.

and the link you suggested (xldynamic.com) was very helpful.

many thanks!!

-jos