Array Calculations for George

G

George Bundy

I have 3 columns of data: one is text and the other two
are numerical. I want to put a formula in one cell which
will:
1) Search the text column for multiple occurences of a
particular text string, then
2) Find values from the corresponding rows of numeric data
and multiply the two together, then
3) Sum the products identified in step 2.

I've been trying to use this array formula but am not
coming up with the desired result: =+IF(POS="OPS",SUM
(MULT*MAX))

Any suggestions?

Thanks!

GB
 
D

Domenic

Hi George,

Try the following...

=SUMPRODUCT((A1:A10="OPS")*(B1:B10)*(C1:C10))

....or assuming that POS, MULT, and MAX are defined ranges, try...

=SUMPRODUCT((POS="OPS")*(MULT)*(MAX))

Hope this helps!
 
G

George Bundy

It works--thanks, Domenic!!

-George
-----Original Message-----
Hi George,

Try the following...

=SUMPRODUCT((A1:A10="OPS")*(B1:B10)*(C1:C10))

....or assuming that POS, MULT, and MAX are defined ranges, try...

=SUMPRODUCT((POS="OPS")*(MULT)*(MAX))

Hope this helps!


.
Click to expand...
 
Top