Array Calculations for George

[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

 

[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!

 

[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!


.