Sum Array - Imbeded OR statement

  • Thread starter Thread starter Jon9977
  • Start date Start date
J

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
 
=SUM((I5:I16={"Overhead","G&A"})*(J5:J16="Sales")*K5:K16)

Array-entered.

HTH
Jason
Atlanta, GA
 
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
 
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?
 
Back
Top