Sumproduct help

[Timmy Mac1]

In a formula where I might have for example

=sumproduct((range_A=1)*(range_B),range_C)

Where range_A, range_B and range_C are random values.

What I would like to do is replace the "=1" part with a reference to a cell which would enable me to change the criterium of the parameter.

So for example I could, at a whim, change it to range_A>3, or range_A=<4.

Hope this explains adequately

I'd be grateful for any suggestions

[tony h]

Don't know how to do it without a macro.

Depending on how many of these you want to do depends on how organised you want to be

for just one you could try:
range("A1")="=sumproduct((range_A" & inputbox("Enter test","=1") & ")*(range_B),range_C)"

If you have got lots to do I would probably set up some formulae on the spreadsheets to complie the text for the formulae and then have a macro which goes through and puts them in as formulae into the required cells.

As you are going to need to know where all the source text is it might be worth setting up a table of target cells and formulae

hope this helps

[RES]

The only way I can see to solve this without VBA
use a helper column and either a SUMIF or COUNTIF
as they are IF functions that accept a text criteria

=countif(A1,criteria)*B1*C1
where criteria refers to a cell with the text in
eg. <>45

you would then need to sum the helper column

all my attempts to turn this into an array formula have failed

Hope this helps a small amount

RES