Entered data for use in formula
Thanks Roger and Stefi. Both those solutions worked perfectly.
Cheers.
"Stefi" wrote:
If I were you I'd structure entered data like this:
A1: condition column - D
B1: sum column - E
A2: row from - 3
B2: row to - 50
In this case the formula:
=SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2)))
Regards,
Stefi
=SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B 1&A2):INDIRECT(B1&B2)))
€˛Stefi€¯ ezt Ć*rta:
=SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):IND IRECT(B2)))
Be careful! Ranges defined in A1,A2 and B1,B2 must be of same length, e.g.
if you enter A2=50 and B2=51 then you get error!
Regards,
Stefi
€˛Mark11€¯ ezt Ć*rta:
I am trying to make formulae that is used regularly be more user friendly.
Is there a way I can enter a range and use that input data in a formula.
e.g. in A1 type "D3" and A2 type "D50" B1 type "E3" B2 type "E50" then use
D3:D50 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D3:D50<1000),--(E3:E50)).
I have tried many variations but none seem to work.
Any help would be appreciated.
|