Entered data for use in formula

[Mark11]

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
D350 and E3:E50 in a formula, e.g. =SUMPRODUCT(--(D350<1000),--(E3:E50)).
I have tried many variations but none seem to work.
Any help would be appreciated.

 

[Roger Govier]

Hi Mark

Try
=SUMPRODUCT((INDIRECT(A1):INDIRECT(A2)<1000)*
INDIRECT(B1):INDIRECT(B2))

 

[Stefi]

=SUMPRODUCT(--(INDIRECT(A1):INDIRECT(A2)<1000),(INDIRECT(B1):INDIRECT(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:

 

[Stefi]

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(B1&A2):INDIRECT(B1&B2)))

Regards,
Stefi

=SUMPRODUCT(--(INDIRECT(A1&A2):INDIRECT(A1&B2)<1000),(INDIRECT(B1&A2):INDIRECT(B1&B2)))

„Stefi†ezt írta:

 

[Mark11]

Thanks Roger and Stefi. Both those solutions worked perfectly.
Cheers.

 

[Mark11]

Actually, it worked for simple sumproduct formulae, but once I tried
=SUMPRODUCT(--(ISNUMBER(FIND($B$8,(indirect($B$3&$B$6):indirect($B$3&$B$7)))),--(indirect($B$5&$B$6):indirect($B$5&$B$7)<$f4)))

Where B8 is text, B3=AB, B5=AH, B6=2, B7=207 and f4=1000
It was supposed to look to AB2:AB207 and find, say, "DIESEL" (f4) and sum
all totals of diesel orders in (AH2:AH207) under 1000.
It just keeps giving #VALUE and says "All arrays must have the same
dimensions"
Clearly I don't know what this means, nor how to fix it.
Any ideas?

 

[Roger Govier]

Hi Mark

Wrong number of parentheses in your formula, it should be
=SUMPRODUCT(--(ISNUMBER(FIND($B$8,(INDIRECT($B$3&$B$6)
:INDIRECT($B$3&$B$7))))),--(INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7)<$F4))

But that will only give you the Count of the number of Items where Diesel is
less than 1000.
If you want the SUM, you need to add the second range AH2:AH207 again.

=SUMPRODUCT(--(ISNUMBER(FIND($B$8,(INDIRECT($B$3&$B$6)
:INDIRECT($B$3&$B$7))))),--(INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7)<$F4),
(INDIRECT($B$5&$B$6):INDIRECT($B$5&$B$7)))

 

[Stefi]

Your request is not very clear, but if B8 contains "DIESEL" then maybe you
need this formula:
=SUMPRODUCT(--(INDIRECT(B3&B6):INDIRECT(B3&B7)=B8),--(INDIRECT(B5&B6):INDIRECT(B5&B7)<F4),INDIRECT(B5&B6):INDIRECT(B5&B7))

Regards,
Stefi

„Mark11†ezt írta:

 

[Mark11]

Thanks again Roger & Stefi.
Roger's solution worked!
Stefi's didn't seem to - not sure why. Maybe because "Diesel" was being
found in a phrase. I agree I wasn't clear, sorry.
Cheers.

 

[Mark11]

....also - Roger is right - Sorry - I pasted the wrong formula! I do count
with that one and sum with another. I blame late afternoon and formula
frustration syndrome!