Sum Array - Imbeded OR statement

[Jon9977]

I am using an array to sum a column of data based upon 2 sets o
criteria in 1 column, and 1 set of criteria in the 2nd.
I want to sum rates (Col K) for Sale's Overhead and G&A.

Col I Col J Col K
Overhead Sales 8%
G&A Sales 3%
Overhead Marketing 20%
G&A Marketing 22%
Overhead Finance 9%
G&A Finance 15%

My array formula which does not work is:
{=SUM((OR($I$5:$I$16="Overhead",$I$5:$I$16="G&A"))*($J$5:$J$16="Sales")*(K$5:K$16))}

I can write 2 different sum formulas, but will have to scale this fo
many more records, and cannot have too long of a string.

Thanks

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(I5:I16={"G&A","Overhead"}),--(J5:J16="Sales"),K5:K16)

 

[Jason Morin]

=SUM((I5:I16={"Overhead","G&A"})*(J5:J16="Sales")*K5:K16)

Array-entered.

HTH
Jason
Atlanta, GA

 

[Peo Sjoblom]

Take off the OR and use + instead of * for those conditions that are OR

or you could use

=SUMPRODUCT(($I$5:$I$16={"Overhead","G&A"})*($J$5:$J$16="Sales")*($K$5:$K$16
))

entered normally



--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Phil]

Jon, I tried your formula and it worked for me. (I also
tried it with the OR() replaced with an addition, and it
worked again.)

You don't say how your formula fails. Perhaps the problem
is a cell set to the wrong type? Perhaps you have
something in the lines you don't show, 7:16 that XL groks
on?