SUMPRODUCT question - referencing cell

[jcooooper]

Hi all,

The below code, I am trying to change the "<1" to reference a cell that contains the value 1.

When I substitute the cell for the hard number, doesn't seem to work. Any ideas?

Thanks!

Range("E21").FormulaR1C1 = _
        "=SUMPRODUCT(SUMIFS(Data_notional_weight,Data_security_type,FI_security_types,Data_duration,[B]""<1""[/B]))"

 

[steve the fish]

What is the cell reference and does it have any absolution, the dollar signs?

 

[jcooooper]

Cell reference will be one column to the left, so D21.

I was hoping to adapt the formula from Range("E21") to Range(E21:E50), but each would look at the column to the left, so trying to avoid absolute references if possible

Hope that makes sense

 

[steve the fish]

Try like this:

Range("E21:E50").FormulaR1C1 = "=SUMPRODUCT(SUMIFS(Data_notional_weight,Data_security_type,FI_security_types,Data_duration,RC[-1]))"

 

[jcooooper]

Try like this:

Range("E21:E50").FormulaR1C1 = "=SUMPRODUCT(SUMIFS(Data_notional_weight,Data_security_type,FI_security_types,Data_duration,RC[-1]))"

Ok that works, but I need the formula to be "< RC[-1]"

I've tried a few variations, one that returns TRUE, when I'm trying to get it to sum product

 

[steve the fish]

You use:

""<""&RC[-1]

instead of just the RC[-1]

 

[jcooooper]

That works, thanks!!

You use:

""<""&RC[-1]

instead of just the RC[-1]