Entered data for use in formula

M

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
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.
 
R

Roger Govier

Hi Mark

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

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:
 
S

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:
 
M

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?
 
R

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)))
 
S

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:
 
M

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.
 
M

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! :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top